Results 1 to 4 of 4

Thread: Pivot Table Macro Error. Runs ok in debug.

  1. #1

    Default Pivot Table Macro Error. Runs ok in debug.

    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:
    Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets("pivottable").Range("A2"), _
    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 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.

  2. #2

    Default Re: Pivot Table Macro Error. Runs ok in debug.

    Update.... I was able to reboot our server but that did not solve the problem.


  3. #3

    Default Re: Pivot Table Macro Error. Runs ok in debug.

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2003
    Luton, England.

    Default Re: Pivot Table Macro Error. Runs ok in debug.

    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.
    BrianB (using XL2003 & 2010)
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts