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:
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
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")
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