Hi SuryaManyam,
I failed to reproduce your issue. I download the file and tried to run the code. I ran the code in last version office (building number 8625.2127) and an insider version(building number 8730.2034), both of them could run the code well.
What's building number of your excel? Could you revert the Excel to previous version make the code work?
Could you create the pivot table manually? If the PivotTableWizard does not work for you, you could try to add a pivot table cache and then create a pivot table via the cache.
Here is the simply example code.
Sub Macro1()
Dim objSheet As Worksheet
Set objSheet = Sheets.Add
Dim objCache As PivotCache
Set objCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Sheet1.Range("A1:H19"), 6)
Dim objTable As PivotTable
Set objTable = objCache.CreatePivotTable(objSheet.Range("A1"))
Dim objField As PivotField
Set objField = objTable.PivotFields("DEPT")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("LOCATION")
objField.Orientation = xlColumnField
Set objField = objTable.PivotFields("SALARY")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "$ #,##0"
ActiveSheet.PrintPreview
End Sub
Best Regards,
Terry
MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading
this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.