Multiple Pivot Tables- Same Source- Update information

[Hurshil]

Hey Guys

I'm trying to find a VBA or Macro that will speed up such a laborous process:

I have multiple pivot tables on different sheets, and multiple pivot tables on the same sheet!

All with one data source, is there a way to make a code that will mean i can update the data source on all the pivot tables in one go?

Thank you so much!!!

 

[Hurshil]

Sorry, by update the data source, currently the problem is they are not looking up the whole set of information, I need a VBA or Macro that will update all 60 odd pivot tables to one source of information on a "Detail" page

I found this code:

Sub UpdateLocalPivotSources()
Dim stSource As String
Dim PC As PivotCache
Dim WS As Worksheet
Dim PT As PivotTable
Dim stNewSource As String
For Each PC In ActiveWorkbook.PivotCaches
stSource = PC.SourceData
If PC.SourceData Like "'SOURCE DATA'!R1C1:*" Then
stNewSource = Sheets("Source Data").Range("A1").CurrentRegion.Address(True, True, xlR1C1)
PC.SourceData = "'SOURCE DATA'!" & stNewSource
End If
Next

' refresh all pivot tables
For Each WS In ActiveWorkbook.Worksheets
For Each PT In WS.PivotTables
PT.RefreshTable
Next
Next
End Sub

But can't get it to work, with my limited coding skills

 

[Hurshil]

Sorry!!

Sub AllWorkbookPivots()


Dim pt As PivotTable


Dim ws As Worksheet


Dim stSource As String


Dim PC As PivotCache


For Each PC In ActiveWorkbook.PivotCaches
stSource = PC.SourceData

If PC.SourceData Like "'Detail'!R1C1:*" Then

stNewSource = Sheets("Detail").Range("A1:AG66600").CurrentRegion.Address(True, True, xlR1C1)

PC.SourceData = "'Detail'!" & stNewSource

End If

Next


For Each ws In ActiveWorkbook.Worksheets





For Each pt In ws.PivotTables


pt.RefreshTable


Next pt

Next ws

End Sub