changing Pivot table name via macro

[Sandip]

Hi,

I have around 20 pivot table in a excel file. One Pivot table on each
sheet. The data source of all the pivot tables is external.

I need to refresh these pivot tables on a daily basis for monitoring
daily changes.

I have kept the name of the Pivot table the same as the Sheet name for
simplicity. However whenever the Pivot tables are refreshed, the name
changes back to Pivottable**.

I would like to have a macro which can change back the Pivot table name
in each of the 20 sheets to the Sheet name after I finish refreshing
all the sheets.

Appreciate if some one can help me on this.

Regards
Sandip.

 

[Guest]

to refresh Pivot Table you can try this command:

Sub AllWorkbookPivotsRefresh()
Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

ws.Activate
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt

Next ws

End Sub

 

[Sandip]

Hi Muhammed,

I think you misread my query.

I have to refresh each table manually via an add-in as it accesses an
external source and each pivot table takes 3 to 10 min to refresh.

The macro I require is to rename each pivot name which automatically
changes to Pivottable**. The Pivot table name needs to be changed back
to the Sheet name.

I have certain macros and reports which filters Pivot fields and hence
accesses all the pivot table by their specific names. However the names
change upon refresh. Currently I go through each pivot table and rename
it with the same name as the sheet name after I finish refreshing all
the 20 pivots.

Regards
Sandip

 

[Tim Marsh]

Hi Sandip,

I have tested this, but a slight modification of the original code might
work as follows: -

Sub AllWorkbookPivotsRefresh()
Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

ws.Activate
For Each pt In ws.PivotTables
pt.name = ws.name
Next pt

Next ws
End Sub

i presume this would give you some kind of error if you have more than one
pivto table per sheet, but as you've said that you haven't... it might just
work! you might to include some sort of error handling for that and/or the
absence of a pivot table on any sheets.

hth,

tim