Solved: Pivot Refresh Problem

Hi guys

I've written the below to open a workbook and refresh the pivot tables in the workbook.

This used to work, however, it's randomly stopped working and after a mornings investigation I'm at a loss as to why. Nothing has changed in either the code or the workbook, but clearly something is wrong. :dunno

All it does now is open the workbook, recalculate the cells, save it and close it. It completely ignores the pivot refresh coding?!

[vba]Sub AllWorksheetPivots()

'opening workbook
Workbooks.Open Filename:="x:\quarter 2.xls"

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

Dim pt As PivotTable

For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt

'Call pivotrefresh

Next ws
'save and close
Workbooks("quarter 2.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close[/vba]

Any ideas?

Phel :thumb

Have you checked the Calculation tab in Options to set it to Automatic?

Another thing: "Maybe" this could be the reason?

[VBA] For Each pt In ActiveSheet.PivotTables
pt.PivotCache.Refresh
Next pt[/VBA]


just guessing here, I'm not very versed in pivots.

ActiveSheet is not changed by being in a For Each

[VBA]
For Each pt In ActiveSheet.PivotTables
[/VBA]

Try

[VBA]
For Each pt In ws.PivotTables
[/VBA]

Paul

Along with the ActiveSheet portion being wrong, which Paul pointed out, I would also caution that if you're going to programmatically open a file, you should probably be checking if the file is already open. That way if you opened it programmatically, you can close it as well, cleaning up behind you. Also, if it's already open, no need to try and re-open it, just set it to a variable as-is.

Basically anytime you see the work Active preceding an object, be wary, be very wary, as you probably don't need it and it will be causing you problems. :)

HTH

Also, why not just do a RefreshAll, not need to loop the worksheets then.

If you have multiple pivottables sourced from the same pivotcache, you can refresh any one of the pivottables (actually, as was pointed out, you refresh the pivotcache) and all of them will be refreshed.

This isn't well-documented. I don't know if a RefreshAll gets around that problem but I know that if you refresh each pivottable/pivotcache individually and some pivottables share the same pivotcache, it will cause the data to reload into the pivotcache for each pivottable which is refreshed (is that clear?). Anyway, refreshing any one of the pivottables for a pivotcache causes all the pivottables connected to that pivotcache to refresh.

Thanks for the help, I've abandoned the individual refresh and gone for the pivot cache and it's solved the issue. However, this has given rise to another problem....I've created a seperate thread for that.

Thank you all for your help. :cloud9: