PivotTable Code Blowing Up

kc_native

Board Regular
Joined
Jan 20, 2009
Messages
176
Folks, I have some code in a maacro that has been working beautifully, but just recently started to blow up on me. The code that is off is as follows:
Code:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Count_Employees_by_Div_Type_W__!R1C1:R1133C6", Version:= _
        xlPivotTableVersion14).CreatePivotTable TableDestination:="Sheet1!R3C1", _
        TableName:="PivotTable4", DefaultVersion:=xlPivotTableVersion14
The error message I am getting says "Runtime error '5': Invalid procedure call or argument". I don't know enough avout VBA to know what part of the code is causing this error, therefore I don't know what to fix.

Would appreciate some help on this. Thanks!
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

par60056

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
  1. 2011
  2. 2010
Platform
  1. Windows
  2. MacOS
It is trying to get data from the sheet named "Count_Employees_by_Div_Type_W__" and put it on "Sheet1" Do both of these sheet names exist? I suspect somebody decided that Sheet1 was blank and they would remove it. Or they cleaned up the other sheet name by removing the ending underscores.
 

kc_native

Board Regular
Joined
Jan 20, 2009
Messages
176
It is trying to get data from the sheet named "Count_Employees_by_Div_Type_W__" and put it on "Sheet1" Do both of these sheet names exist? I suspect somebody decided that Sheet1 was blank and they would remove it. Or they cleaned up the other sheet name by removing the ending underscores.

Your response helped me figure it out (well, sort of). I had already discovered that my base worksheet that had the data on it had changed when we switched to MicroStrategy V9. It is no longer exporting reports with the "extra" column before the metrics, and therefore my range in the pivot table contained a column on the end with no heading. When I went back to re-test it, I was starting at this code, instead of the line just before (which I didn't include) that adds the new sheet for the PivotTable (Sheets.Add).

Thanks!

Thanks for helping me to see an obvious mistake in ommiting Sheets.Add in my test run.
 

kc_native

Board Regular
Joined
Jan 20, 2009
Messages
176
One other thing that has confounded me with the PivotTable code is that from time to time I have to change the number of the Pivot (PivotTable2 vs PivotTable1, PivotTable3 vs PivotTable2, etc.). Now I have had to up that to PivotTable4, I assume from my repeated attempts to get the macro to work. What causes Excel to change that number designation?
 

par60056

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
  1. 2011
  2. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Personally I always give my tables meaningful names but that isn't required. like "LengthTable" or "ClientsTable" One of the things that this helps is I get real errors when I copy a section of code and don't fix everything in it for the new use.

I think that there is a PivotTableCache associated with each sheet. Deleting the pivot table from a sheet does not remove everything about it from the cache.

As you said the line before the create was to add a sheet. I always put my table onto a blank sheet. Even when testing code and having to recreate the table because I missed something or got an error, I delete the sheet and move the process back to before the sheet.add.

I am trying to get into the habit of not just calling "Sheets.Add" I now try to do Sheets.Add(after:=sheets(1)).name="NewSheetName" so that it is named right away. When I find code referencing "Sheet1" I know somebody used the recorder and did not mean this to be production level code because adding a sheet can give any number.
 

kc_native

Board Regular
Joined
Jan 20, 2009
Messages
176
Yeah, I just realized that part of what I am doing in the code I pasted above is naming the Pivot, so I just replaced "PivotTable4" in that code with "Headcount Pivot" and then renamed all references to that pivot in the rest of the code. Works like a charm! Duh! Sorry, VBA isn't exactly intuative. I am learning by my mistakes. Its a slow process unfortunately.
 

par60056

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
  1. 2011
  2. 2010
Platform
  1. Windows
  2. MacOS
I've been doing it for 15 years and simple mistakes are the ones that kill you. So I try to make sure that it is harder to make a simple mistake without it blowing up dramatically.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top