UPDATE - I changed PRange to PRange.Address in the Set PTCache line of code and it worked. Can anyone explain the technical reason behind this?
Good afternoon everyone,
I am having the damndest time trying to have a PivotTable created via VBA. Here is the setup:
I have a sheet with one tab called Source
This tab has source information consisting of columns A:AF and a varrying # of rows each day.
My goal is to create two new worksheets and have a PivotTable appear on each. They both show pretty much the same data with the exception of one sheet having an additional data field. I am using Excel 2010.
I've broken the code into three blocks (I will add blocks 2 and 3 if necessary but I feel like they aren't causing the problem):
1) Establishes static variables such as sheet names and output variables
2) Creates the first sheet and PivotTable on it
3) Creates the second sheet and PivotTable on it
My code continuously fails at the "Set PTCache = " line
I've scoured the net and tried different combinations of suggested changes but they all generate the same error - Run-Time Error 5, Invalid Procedure Call.
Thanks guys.
Bookmarks