VBA - change all pivot table data sources to named range

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

Try to use this code:

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

   myRange = Range("DataPullForUseFY17").Parent.Name & "!" _
      & Range("DataPullForUseFY17").Address(ReferenceStyle:=xlR1C1)
   
   For Each St In ActiveWorkbook.Worksheets
      For Each pt In St.PivotTables
         .ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:=myRange, _
            Version:=xlPivotTableVersion15)
      Next
   Next

   Application.ScreenUpdating = True
End Sub

Regards,
Antonio

Thanks, I'll try it out

Originally Posted by antoka05

Try to use this code:

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

   myRange = Range("DataPullForUseFY17").Parent.Name & "!" _
      & Range("DataPullForUseFY17").Address(ReferenceStyle:=xlR1C1)
   
   For Each St In ActiveWorkbook.Worksheets
      For Each pt In St.PivotTables
         .ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:=myRange, _
            Version:=xlPivotTableVersion15)
      Next
   Next

   Application.ScreenUpdating = True
End Sub

Regards,
Antonio

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