Trying to insert a private sub into new workbook that is created via macro

I am working on automating a report that pulls credit card spend transaction data, organizes transaction data by approving manager, makes pivot tables from that data, copies the pivot table and underlying data to a new sheet, resets the pivot table source to the data on the new sheet, then saves and closes the new sheet with the pivot table and data that was copied from the source sheet.

I am running into a problem where when the new workbook is opened when the user is trying to change a filter Excel shows an error that the table was saved without the underlying data.

I have tried to have the script open the new sheet (after previously closing it), refreshing the data, then closing and saving it, but that doesn't seem to solve the problem.

Is there a way that I can insert a private sub into the new workbook that runs when the workbook is opened that refreshes the pivot table data to avoid the error?

Thanks in advance for any help.

I was able to find a problem to my own problem, I am not sure if there is a better way, but what I did was to make a mastersheet in my data workbook that has this code on the sheet,

Public sheetopened As Boolean

Private Sub worksheet_activate()
If sheetopened = False Then
ThisWorkbook.RefreshAll
sheetopened = True
End If
End Sub

I then put code on the "ThisWorkbook" sheet in the VBA editor so that the master sheet was copied every time that a new sheet is created so that the new sheet has the update code. Here is that code:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim tmpName As String

tmpName = Sh.Name
Sheets("MasterSheet").Copy Before:=Sheets(Sh.Name)
Application.DisplayAlerts = False
Sheets(Sh.Name).Delete
Application.DisplayAlerts = True
Sheets("MasterSheet (2)").Name = tmpName
Sheets(tmpName).Visible = True
Sheets(tmpName).Activate
End Sub

This then made it so that when my normal macro runs to make the pivot tables and copy them to new workbooks the update code is copied with it. The only thing that I had to do was to make it so that the user has to change sheets to select the sheet with the pivot table in order for the update code able to work as it will not run when the workbook is opened unless the user leaves and returns to the sheet with the pivot table. I hope this helps anyone who is having the same problem that I was having.