Change data source from Pivot

Question

I am creating a new file based on an old one and now I am trying to change the data source from a pivot chart (still linked to the old file). The new data source should be from a table on the new workbook. Can you please help?

    Dim pt As PivotTable
    Dim MyData As Excel.ListObject
    
    Set MyData = Application.Range(Europe).ListObject
    
For Each pt In ActiveWorkbook.Worksheets("Chart - City").PivotTables
         pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
            (SourceType:=xlDatabase, SourceData:="MyData")
Next pt

Answer 1

The SourceData parameter for the PivotCaches.Create method expects a string, not a listobject.

Try with:

SourceData:=MyData.Name