Excel VBA Error 9 with PivotTables

[saisis]

Hello, I recorded a macro to create and setup a pivottable from Sheet "Tags" to Sheet "Flt Hours". When I ran the recorded macro, I got a message saying Error 9: subscript out of range. The highlighted code is:

ActiveWorkbook.Worksheets("Flt Hrs").PivotTables("PivotTable1").PivotCache. _
CreatePivotTable TableDestination:="Flt Hrs!R3C6", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion14
​

The last line " , DefaultVersion:=xlPivotTableVersion14 " is where it says the error is occurring. Here is the rest of the code:

Sheets("Tags").Select
Range("A2").Select
ActiveWorkbook.Worksheets("Flt Hrs").PivotTables("PivotTable1").PivotCache. _
CreatePivotTable TableDestination:="Flt Hrs!R3C6", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion14
Sheets("Flt Hrs").Select
Cells(3, 6).Select
ActiveWorkbook.ShowPivotTableFieldList = True
'Sheets("Flt Hours").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Primary Fault")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Partner")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Downtime Category")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Interval Hours"), "Sum of Interval Hours", xlSum
​

I am using Windows 7, Excel 2010. The code works to put in the fields for the PivotTable, but fails when the table is being created. I'm familiar with VBA, but frankly, I'm stumped. I've looked at several different posts, and nothing I've seen matches this problem.

 

[mole999]

how many pivot tables exist, I had problems with more than one PivotTable1, my cure was to rename to different

do all your data have a header value, if i have problems thats where i start to look and add underscore inplace of blanks i.e Primary Fault / Primary_Fault

 

[saisis]

Thank you mole999.



I've been working on trying to generalize the pivot table names so that it will work with any pivot tables, however the name is not the current issue as I have found out.

What I've been doing to test the macro is closing all open excel documents, then reopening the wb with the recorded macro and opening up a new wb for the macro to run on. This resets the counts for tables, charts, objects etc. for the new wb to "1". The bug happens on line "DefaultVersion:=xlPivotTableVersion14" when PT is being created.

If I remark out the creating PT code, create a PT manually, and let the macro run, it works fine and has no bugs. I wonder why I get the error with that one line since default version 14 is for Excel 2010, which is the Excel version I am using.

Also, if I only remark the line "DefaultVersion:=xlPivotTableVersion14" I get an error saying it has to be in the code.

I will try your suggestion to replace the spaces with underscores and post my results. Again, thank you!

 

[saisis]

Thank you so much mole999! I finally got the opportunity to try your suggestion to replace the spaces with underscores. Just doing that did the trick! Now my macro is working flawlessly! Again, Thank You!!! I will keep this tip in mind next time I need to work on a macro to create a pivot table

 

[mole999]

glad it sorted your issue