VBA Pivot Table Method 'PivotTableWizard' of object'_Worksheet' Failed

Question

I have been trying to automate a pivot table using various methods, yet each one has given me an error. This has been mostly due to the fact that I am trying to create a variable range and pivot tables may already exist in the spreadsheet I am using.

This most recent method worked on a test spreadsheet, however it did not work on the actual spreadsheet that I would like to use it on. The spreadsheet already has a pivot table, which may be the issue? The code is stopped at set objTable =Sheet1.PivotTable

Dim objTable As PivotTable
Dim objField As PivotField
    ActiveWorkbook.Sheets("Roster").Select
Range("A1").Select

Set objTable = Sheet1.PivotTableWizard

Set objField = objTable.PivotFields("Member Type")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("AIA Member Status")
objField.Orientation = xlColumnField
Set objField = objTable.PivotFields("Customer")
objField.Orientation = xlDataField
objField.Function = xlCount

Set objField = objTable.PivotFields("Expire Date")
objField.Orientation = xlPageField

objTable.PivotFields("Expire Date"). _
CurrentPage = expireyear

objTable.PivotFields("Member Type") _
    .PivotItems("Cornerstone Partners").Visible = False
objTable.PivotFields("Member Type") _
.PivotItems("Honorary AIA").Visible = False
objTable.PivotFields("Member Type") _
.PivotItems("Honorary Fellow").Visible = False


objTable.PivotFields("AIA Member Status") _
.PivotItems("Proforma").Visible = False

Range("A6, A8").Select
Range("A8").Activate
Selection.Group
Range("A6").Select

objTable.PivotFields("Member Type2").PivotItems( _
"Group1").Caption = "Architect"

objTable.PivotFields("Member Type2").PivotItems( _
"Architect").ShowDetail = False

Range("A7,A8,A10").Select
Range("A7").Activate
Selection.Group
Range("A7").Select
objTable.PivotFields("Member Type2").PivotItems( _
"Group2").Caption = "Emeritus"

objTable.PivotFields("Member Type2").PivotItems( _
"Emeritus").ShowDetail = False

objTable.PivotFields("Member Type2").PivotItems( _
"Associate").ShowDetail = False

objTable.PivotFields("Member Type2").PivotItems( _
"International Associate").ShowDetail = False

objTable.PivotFields("Member Type2").PivotItems( _
"International Associate").ShowDetail = False

objTable.PivotFields("Member Type2").PivotItems( _
"Allied").ShowDetail = False

objTable.PivotFields("Member Type2").PivotItems( _
"Allied").Position = 5

objTable.PivotFields("Member Type2").PivotItems( _
"Emeritus").Position = 4

Thank you in advance

`.PivotTableWizard`? Try `Set objTable = Sheet1.PivotTables(1)` — Siddharth Rout, Aug 13 '16 at 14:32
Also, [avoid using select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) and use `With ... End With`. — Brian, Aug 13 '16 at 15:09
Siddarth, it still does not work. Do I need to make a sheet 1 before performing the operation? Also, do I need to change the index number if a pivot table already exists in the file? — Steven, Aug 13 '16 at 16:43

winghei · Accepted Answer · 2016-08-13T19:31:49.957

1

For existing pivot table, use PivotTables and make sure the worksheet is existing. try:

With ThisWorkBook.Sheets("sheet_name")     
     Set objTable = .PivotTables("pivot_name or index") 'use the correct index or name if you have multiple 
     '.. do more stuffs
End With

to create a new one:

With ThisWorkbook
    Set objtable = .PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "sheet_name!A1:D20", Version:=xlPivotTableVersion15).CreatePivotTable _
    (TableDestination:="sheet_name!R1C6", TableName:="thePivotName", DefaultVersion _
    :=xlPivotTableVersion15)
    '.. do more stuffs
End With

edited Aug 13 '16 at 19:31

answered Aug 13 '16 at 17:27

winghei

632
4
9

I am trying to make a new pivot table unrelated to the one that is already there. Wouldn't the above be missing the data source? – Steven Aug 13 '16 at 18:18
you have to create the new pivot table first then reference that pivot table. – winghei Aug 13 '16 at 19:34
Thank you for the help, but it is giving me Run-time error '5' – Steven Aug 18 '16 at 15:56
Sub Pivot() Dim objTable As PivotTable Sheets.Add.Name = "Pivot" With ThisWorkbook Set objTable = .PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Roster!A1:AR87929", Version:=xlPivotTable14).CreatePivotTable _ (TableDestination:="Pivot!B3", TableName:="PivotableXL", DefaultVersion _ :=xlPivotTableVersion14) End With End Sub – Steven Aug 18 '16 at 15:57
change `xlPivotTable14` to `xlPivotTableVersion14` and `Pivot!B3` to `Pivot!R3C2` – winghei Aug 18 '16 at 17:40

score 0 · Answer 2 · answered Aug 14 '16 at 13:03

Try using the piece of code below in your full code, it searches sheet "Roster" (where you want to place your Pivot Table) for existing Pivot Tables.

If it already has Pivot Table, then you only need to refresh the Source Data Range and the Pivot Table. If it doesn't, then create a new Pivot Table with all of your Fields.

Dim shtPivot                        As Worksheet
Dim objTable                        As PivotTable
Dim PvtTableCounter                 As Integer

' modify to your desired Pivot Table location
Set shtPivot = ThisWorkbook.Sheets("Roster")

For Each objTable In shtPivot.PivotTables
    PvtTableCounter = PvtTableCounter + 1
Next objTable

' "Roster" sheet doesn't have any Pivot tables in it >> create it
If PvtTableCounter = 0 Then
    ' create your Pivot table and all your fields here...

Else ' There is at least 1 Pivot Table in "Roster" sheet
    ' just modify the source data range and refresh the Pivot

End If

VBA Pivot Table Method 'PivotTableWizard' of object'_Worksheet' Failed

2 Answers2