Excel VBA Error 9 with PivotTables

saisis

New Member
Joined
Oct 21, 2014
Messages
9
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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,513
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 21, 2014
Messages
9
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

New Member
Joined
Oct 21, 2014
Messages
9
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!!! :biggrin: I will keep this tip in mind next time I need to work on a macro to create a pivot table :)
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,513
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
glad it sorted your issue
 

Watch MrExcel Video

Forum statistics

Threads
1,108,674
Messages
5,524,204
Members
409,565
Latest member
Suilenroc

This Week's Hot Topics

Top