Files are located here: https://drive.google.com/file/d/1C4H...ew?usp=sharing

I'm looking for a way to refresh a powerpivot table in a spreadsheet. I have 4 pivot tables - 3 are regular pivot tables, and 1 (PivotTable_ServiceReminders) is a powerpivot. I generate the full report first, then copy 5 tabs to new workbooks to create the regional reports, update and refresh the pivot tables and data connection in each region file (currently there are 31 but more will come), and press a button to link the filters.

The code I have is: 


Sub Refresh_PivotTables()
Dim ptcount As Integer
Dim serviceptsource As String
Dim meterptsource As String


ActiveWorkbook.Sheets(shtRemindersPivot.Name).Activate
serviceptsource = ActiveWorkbook.Sheets(ShtReminders.Name).ListObjects(1).Name
meterptsource = ActiveWorkbook.Sheets(shtMeter.Name).ListObjects(1).Name

ActiveWorkbook.Connections("WorksheetConnection_Meter Entry report - template - with service reminders.xlsm!Table_ServiceReminders").Name = "Table_ServiceReminders"
ActiveWorkbook.Connections("Table_ServiceReminders").Refresh

ActiveSheet.PivotTables("PivotTable_NoReminders").ChangePivotCache _
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        serviceptsource, Version:=8)

ActiveSheet.PivotTables("PivotTable_NoReminders").ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Table_ServiceReminders")
ActiveSheet.PivotTables("PivotTable_NoReminders").PivotCache.Refresh


ActiveWorkbook.Sheets(shtMeterPivot.Name).Select

For Each pt In ActiveWorkbook.Sheets(shtMeterPivot.Name).PivotTables
    ActiveSheet.PivotTables(pt.Name).ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=meterptsource, Version:=8)
    ActiveSheet.PivotTables(pt.Name).PivotCache.Refresh
Next pt

End Sub

Note: A lot of that was updated from what's in the file, as I was also initially having issues getting the pivot tables to refresh reliably, but I seem to have fixed that while posting.

But I can't get the PowerPivot to update. The source for it is the table in the Service Reminders tab so I go into the properties window and remove the link to the main file from the Command Text to just leave "Table_ServiceReminders" and it works. I found something that said to create the pivot using the Data tab instead of the PowerPivot tab, but after I did that, I got an error "You cannot copy or move a group of sheets that contain a table" and when I manually copy it individually, it tells me about naming conflicts.