Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivottable Refresh Problem (2002 SP2)

    I have tried the following 2 code snippets to refresh a query table

    Sheets("Sheet2").PivotTables("PivotTable1").Refres hTable

    Sheets("Sheet2").PivotTables("PivotTable1").PivotC ache.Refresh

    Both of them work only if the active cell on Sheet 2 is within the pivottable. If it is not, I get a "Reference is not valid" error.

    (In fact there are 2 pivot tables on the sheet, PivotTable2 is a copy of PivotTable1 so both tables update when PivotTable1 is updated).

    I guess my questions are:

    1) What is the difference between the 2 refresh options
    2) Since I am supplying a reference to the object, why does the activecell need to be within it to get a valid reference?
    3) Is there a way around this error (apart from selecting the pivot table)?

    TIA

  2. #2
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivottable Refresh Problem (2002 SP2)

    As a bit of an update on this one, I have discovered that the activecell for sheet 2 can be anywhere in the columns covered by the pivottable and then the refresh will work.

    But I cannot seem to move the active cell on Sheet2 without making it the Activesheet, so I am a bit stuck as to how to ensure that I can always refresh the pivottables.

    To make things a bit clearer, I've attached a sample sheet.

    Place the activecell on Sheet2 anywhere in columns A-D and run the macro MD in Module3 and it will run fine, regardless of which sheet is active at the time it is run.

    Now place the activecell in any other column on sheet 2 and the "Reference is not valid" error will pop up.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivottable Refresh Problem (2002 SP2)

    Problem solved. I had a faulty dynamic range reference to my database. But what a wierd way to show as an error???

Posting Permissions

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