Problem: I am trying to copy a Pivot Table (linked to data) and Paste that Pivot Table in a new workbook. All the while keeping the functionality of the Pivot Table intact, but have the data link be removed as the data of the new Pivot Table must remain static.
My code copies correctly and breaks the link, but the new Pivot Table has no functionality. Which makes sense, but I can't find a solution to this problem. The data link must be removed as I don't want the possibility of the static table being refreshed. This is for the purpose of keeping monthly statements.
Private Sub Auto_Open()
'variables
Dim excelDocPath As String, newfilePath As String, newfileName As String, NewBook As Workbook, MasterBook As Workbook, i As Integer
'initialize strings
excelDocPath = ActiveWorkbook.Path & "\FILE_1.xlsx"
newfilePath = ActiveWorkbook.Path & "\FOLDER\"
'refresh linked master excel workbook
Set MasterBook = Application.Workbooks.Open(excelDocPath, UpdateLinks:=True, ReadOnly:=False) 'create reference to master excel workbook
MasterBook.Worksheets("Sheet1").PivotTables("PivotTable").PivotCache.Refresh 'refresh the pivot table source link
MasterBook.RefreshAll 'refresh all links
MasterBook.Save 'save changes
MasterBook.Close 'close workbook
'initialize new file name with current date
newfileName = "FILENAME (" & Replace(Date, "/", "-") & ").xlsx"
'copy master doc and save as newfileName
FSO.CopyFile excelDocPath, newfilePath & newfileName 'copy temple file and rename accordingly
Set NewBook = Application.Workbooks.Open(newfilePath & newfileName, UpdateLinks:=xlUpdateLinksNever, ReadOnly:=False) 'make reference to new workbook
NewBook.Activate 'make workbook the active workbook
'loop through workbook connections and delete all
For i = ActiveWorkbook.Connections.Count To 1 Step -1
ActiveWorkbook.Connections.Item(i).Delete
Next
NewBook.Save 'save changes
NewBook.Close 'close new workbook
End Sub
