Create Pivot table using vb script error

Question

Hi All,

I have a excel macro which creates pivot table reading data from a sheet , it worked fine until I got the latest Excel 2016 version installed in my work PC. After the upgrade when I run the macro it is crashing excel forcing me to kill it in the task manager process. On debugging observed it is happening when executing below line.

Set objTable = Sheet1.PivotTableWizard

I referred to a code from Microsoft website to try out if something else in my code is causing issue , but it is the same. Below is the URL

URL : https://msdn.microsoft.com/en-us/library/office/hh243933(v=office.14).aspx#CreatingPivotTable_CreatingPivotTableReport

Sample code copied below.

Sub CreatePivot()
  
  Dim objTable As PivotTable, objField As PivotField
   
  ActiveWorkbook.Sheets("RAW").Select
  Range("A1").Select
  
  Set objTable = Sheet1.PivotTableWizard
                        
  
  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

I tried browsing but no suggestions found. 

Thanks in advance.

Answer 1

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 [e-mail].