Is it possible to refresh pivot table(s) without knowing name of the pivot table.

[ThrottleWorks]

Hi,

Is it possible to refresh pivot table(s) without knowing name of the pivot table.

I am writing below mentioned code to refresh pivot table.
‘Worksheets("Summary").PivotTables("PivotTable1").PivotCache.Refresh’

Before refreshing pivot, I am checking if pivot table exist in worksheet.

My doubt is, suppose pivot table name is changed in future then macro will give bug.

Is it possible to refresh all the existing pivot tables in the worksheet without knowing their name.

Can anyone please help me in this.

PS - using below mentioned code to check if there are pivot table(s) in worksheet.

For iCtr = 1 To Worksheets("Summary").PivotTables.Count
    Next iCtr
    If iCtr = 1 Then
            MsgBox "No Pivot table in ABook.name "
            ABook.Close
            End
        Else
            Worksheets("Summary").PivotTables("PivotTable1").PivotCache.Refresh
    End If
    iCtr = 0

 

[Luke M]

Just like with the worksheet name, you could give the index number rather than name. Assuming it's the first/only PivotTable on sheet:

Worksheets("Summary").PivotTables(1).PivotCache.Refresh

Or, my personal preference (unless you have external data queries)

'Refresh ALL PivotTables w/o knowing their names, or checking if they exist
ThisWorkbook.RefreshAll

 

[ThrottleWorks]

Hi @Luke M sir, thanks a lot for the help !
Yes, it is the only pivot table on the sheet.

I will be using 'ThisWorkbook.RefreshAll' (changed workbook name).
I guess this will refresh all the formulas, pivot tables.

Have a nice day ahead.

 

[chirayu]

came up with this. quite rudimentary but should do the job

Sub Pivot_Refresh()

Dim ws As Worksheet
Dim pvt As PivotTable

For Each ws In ActiveWorkbook.Worksheets
 
    For Each pvt In Worksheets(ws.Name).PivotTables
 
        Worksheets(ws.Name).PivotTables(pvt.Name).PivotCache.Refresh
     
    Next

Next

End Sub

 

[ThrottleWorks]

Hi @chirayu thanks a lot for the help. Have a nice day ahead.