VBA - change all pivot table data sources to named range

[sportsnut898]

Hi all,
I need a macro to change all the pivot table data sources in a workbook at once. It will be changed to a named range (DataPull...) as you can see below. The code I have so far returns an error.

Thanks!!!



Sub ChangePTSource2()
'Update 20141112
Dim St As Worksheet
Dim pt As PivotTable
Application.ScreenUpdating = False

For Each St In ActiveWorkbook.Worksheets
For Each pt In St.PivotTables
.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="DataPullForUseFY17", Version:=xlPivotTableVersion15)
Next
Next

Application.ScreenUpdating = True
End Sub

[antoka05]

Try to use this code:

Please Login or Register  to view this content.

Regards,
Antonio

[sportsnut898]

Thanks, I'll try it out

[sportsnut898]

Try to use this code:

Please Login or Register  to view this content.

Regards,
Antonio

I get a "invalid or unqualified reference" error on the .changePivotCache line