Change pivot table source via VBA?

[TheWennerWoman]

I have a master file containing details of payroll for about 100 cost centres. I have managed to put together some code that splits it out into individual files. So, sample data

Cost centre (for Posting)	Element Type	Payroll Reference No	Element	Role	Sum of Value
C0010	Admin	5778​	Basic Salary	Driver	100​
C0010	Admin	5778​	Employers NI	Driver	100​
C0020	Admin	3221​	Basic Salary	Revenue Manager	100​
C0020	Admin	3221​	Employers NI	Revenue Manager	100​

My code will generate two files for this sample, one named C0010, the other named C0020. I'm doing this by copying the master sheet and a pivot table into a new file and then removing anything I don't need.

My problem is the pivot table. It is remembering the link to the master file. I tried this

Sub Change_Pivot_Source_Data()

Dim pt As PivotTable

For Each pt In ActiveWorkbook.Worksheets("Pivot Report").PivotTables
         pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
            (SourceType:=xlDatabase, SourceData:=Sheet1.Range("B2:G50000"))
 
Next pt

End Sub

But this isn't working, the pivot table in the new files are still pointing to the master file. Is there a quick fix or would I be better off creating a pivot table in each file from scratch? If the latter, how easy is that in VBA?

Thank you for reading.