Hi

I'm using MS Excel 2003 SP 3 and I'm having an issue with GetPivotData formula, which I believe is due to a macro I have to update pivot tables.

The formula works, but when I update the Pivot tables (by macro), it causes the tables (with the GetPivot data formula in) to use the same values as the top of the list rather than the correct data. If I double click the cell and enter it returns to the correct value, I then have to drag the formula down to rectify each row. It doesn't do this if I refresh the tables individually - Although I do have 59 pivots so this would be very time consuming.

The macro is

ActiveSheet.PivotTables("pivottable1").RefreshTable
ActiveSheet.PivotTables("pivottable2").RefreshTable
etc etc

Has anybody come across this problem and if so how can I still use macro functionality to update the tables without then having to create a new macro to drag and drob the formula - which is what I'm considering doing?

Background to the spreadsheet is:

The pivot tables are pulling data from sheet "Matrix" which is customers running vertically and the products running horizontally (it shows distribution for each product against each customer).

The "analysis" tab shows data pulled from the Pivot Tables in a more user friendly format showing the customers how many of each product is in each customer and the % value based on how many of that type of customer there actually are.

If any of you can think of an alternative way than pivots to do this - then please feel free to let me know... I've tried Sumproduct and array formulas but they don't appear to work. I've posted this in Programming as I believe it is the Macro that is causing the problem, so I apologise if it's in the wrong group...


I hope this makes sense to you and any advice would be gratefully received