Refresh unknow amount of Pivot Tables /30secs

[MarkCBB]

Hi there,

I am trying to write some code that will refresh an unknown about of Pivot tables every 30 seconds. The Pivot tables are on different worksheets but all are in the same workbook. the name/numbering (PivotTable1,2,3) of the pivots are not contiguous.
I have just started with VBA (reading my first book) this is what I have got so far.

Sub RefPVT()
 ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
 ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
 ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
 ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
 
End Sub

Sub Macro1()
Application.OnTime Now + TimeValue("00:00:30"), "RefPVT"
End Sub

As you can see there, this is not working according to the above mentioned parameters.

 

[dave3009]

Have you tried just refreshing everything

ActiveWorkbook.RefreshAll

 

[njimack]

I haven't tested the below code, but something like this should work:

Sub RefPVT()

Application.ScreenUpdating = False

Dim WS As Worksheet
Dim PT As pivottable

For Each WS In ThisWorkbook.Worksheets

    For Each PT In WS.PivotTables
        PT.PivotCache.Refresh
    Next PT
    
Next WS

Application.ScreenUpdating = True

End Sub

 

[MarkCBB]

Perfect! Thanks guys!

 

[MarkCBB]

Ummm, I thought i know how to make it run every 30sec. but the code above only worked once. then I tried tthe Activeworkbook.RefreshAll and i placed it in a Onchange sub, amd it crashed.

 

[dave3009]

The refreshall option may not be available in version of excel before 2007, I can't check so perhaps you should stick with Neils suggestion.

As for the ontime method you need to call the name of the macro that contains the Ontime from the procedure it runs..In air code

Sub ontime

ontime 30secs, "Procedure"

End Sub

Sub Procedure

Do stuff

Call Ontime

End Sub

Etc

 

[MarkCBB]

Hi Dave,

Using 2010. its working perfectaly now. thanks you. Can you recommend some books for me to read. I am very new to VBA but would like to be alot better.

Thank-you.