Pivot Table Macro Error. Runs ok in debug.

pkstoll

New Member
Joined
Oct 4, 2004
Messages
3
We are running Windows Server 2003-Terminal Server with Excel 2002.

I've been working on a macro to create a pivot table. Everything was going fine until I tried to add a 2nd pivot table consolidating the data by week (my original pivot table was by day). I don't have a lot of experience with this stuff but it appeared that I could not have both in the same workbook. Regardless of whether that is correct or not, I decided to forget about the weekly consolidation so I took out all of the code and put it into another workbook figuring I would play with it later. Now I can not get my original workbook working correctly and I'm getting some weird behavior.

If I open the workbook and try and run the macro it gives me a MS VB Run Time Error 1004. It says: "The pivot table field name is not valid. To create a Pivot table report, .....etc.". If I say Debug it takes me to my macro on the following line:
Code:
Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets("pivottable").Range("A2"), _
        TableName:="PivotTable1")
If I press F8 to step through it gives me another MS VB Run Time Error 2147417848(80010108). Method "createPivotTable" of object "Pivotcache" failed.

This is what is strange; if I open the workbook then immediatly open the VB Editor, find my Module and Macro then put a Break on the first line of my Pivot table programming then I run the Macro from the beginning. It'll stop at my break point then I can step through my several lines of code getting through the error line with no problems. Once past, I press run to pick it back up and it cruises to the end. I do not go to the Workbook-I stay in the VB Editor.

I ran another test doing exactly what I did above but I didn't put in a code break and then if I ran the macro; it errors out exactly as it does when running from the Excel Workbook.

I've searched on MrExcel.com and on MS KnowledgeBase and tried various things but nothing is working. I used the immediate Window to check my Active workbook, Active Sheet and other stuff but it all seemed ok. I found that 2nd error on MS Knowledgebase but not specifically for "createpivottable of object......". That error really seems strange but perhaps it isn't relevent since it isn't the first error.

Other items to note: I've logged off and back onto our server. The server won't shut down until Saturday but I could probably get everyone off at some point and reboot if that seems necessary. My original pivot table code came from the book: "VBA and Macros for MS Excel" (Mr. Excel's book).

Man, I don't want to waste anyone's time on this. I hope I'm not doing something obvious and stupid. If needed I can post more code (it might not be pretty since I'm just learning. I don't feel as if I grasp the concepts yet.).

Thanks for any help.
pk
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Well, I have success. Not sure what exactly did the trick but it is working now. In an effort to possibly help someone in the future I'll list the things I did however as I said, I'm not sure what made the difference. I was not very diligent at changing only 1 thing at a time.

-Point of interest: In the code right before the pivot table stuff I added code to activate the workbook and the pivot table sheet. Then when I stepped through the code it did error. Adding these lines made the code error consistantly regardless of running it in the sheet or stepping through in the VB editor.

-I read on MS Knowledgebase about unqualified calls. I saved out the worksheet to a new name then began messing with the code. I removed all references to the workbook name.
- I renamed all of my variables.
- I removed the recommended code that clears the PTCache.
- I deleted the pivottable sheet and recreated it within the macro.
- Point of interest: If I created a pivottable sheet and left it as the active sheet the macro would fail at the same point (error 1004). If however I created the pivottable sheet then made the active sheet the one that contained all of my data then it stepped through fine.
- Point of interest: At the end of the macro I was deleting the pivot table source data. I'm still doing that but I'm also deleting the pivottable sheet. Perhaps this was a major contributing factor.

I think that is it. Hope this helps someone in the future.
pk
 
Upvote 0
Thanks for the information. Could be useful.

A couple of additions :-

1. The macro code module could have been corrupted. Solution - copy/paste to Notepad, delete module, copy/paste from Notepad to a new module.

2. I get similar messages about invalid field when importing Excel to Access (or it helpfully imports as "Field 12" or whatever). This is caused by my adding a temporary column to the base data to do calculations and then just deleting the cell contents instead of the entire column. Must be a "feature, not a bug" but I cannot figure out the plus factor.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

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
Back
Top