Pivottable Refresh Problem (2002 SP2)

Topic

New Reply

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

Sheets(“Sheet2”).PivotTables(“PivotTable1”).RefreshTable

Sheets(“Sheet2”).PivotTables(“PivotTable1”).PivotCache.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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote