Save data source macro

[balaji3081]

Hi,

I have recorded a macro to save the data source in the background of three pivot tables, The macro is too specific to the three pivot I have, is there a way that it works on any pivot

Sub Pivot()
'
' Pivot Macro
' save data source
'
' Keyboard Shortcut: Ctrl+e
'
    ActiveSheet.PivotTables("PivotTable6").SaveData = True
    ActiveSheet.Next.Select
    ActiveSheet.PivotTables("PivotTable1").SaveData = True
    Sheets("Project Driven Expenses").Select
    ActiveSheet.PivotTables("PivotTable2").SaveData = True
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
    ActiveSheet.Previous.Select
    ActiveSheet.Previous.Select
End Sub

 

[Mr. Mahesh]

Hi Balaji,

Please find below procedure.

Sub SaveDataSource()
Dim pt As PivotTable
Dim ws As Worksheet
Application.ScreenUpdating = False
 
For Each ws In ActiveWorkbook.Worksheets
'Looping each and every sheet
    For Each pt In ws.PivotTables
    'Updates each and every pivot on sheet
        pt.SaveData = True
    Next pt
Next ws
 
Application.ScreenUpdating = True
End Sub

 

[balaji3081]

Great, this is very helpful.

Thanks
Bala

 

[Mr. Mahesh]

Good to hear

 

[Smallman]

This is all I usally use to update all pivot tables.

Sub GetemALL()
    ThisWorkbook.RefreshAll
End Sub

Take care

Smallman