I am using the code below to update pivot tables in my workboook with new source data. This works great, but some of my worksheets have multiple pivot tables on them. The code below will update only the first pivot table on each worksheet. Can someone help me refine this macro in order to update worksheets that have more than one pivot table, while also moving on to update pivot tables on the other worksheets? (EX. update all pivot tables in the workbook with new source data, even if there is more than one pivot table per worksheet).
Additionally, to keep my file size small, should I be working with pivot cache and update my pivot tables that way? Any advice and sample code would be great.
Dim PT As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each PT In ws.PivotTables
PT.SourceData = "Data!" & Range("A1:CN50000").Address(True, True, xlR1C1, False)
PT.RefreshTable
Next PT
Next ws
Thanks for the help!
Bookmarks