Macros and Updating Pivot Table Sources

[TheTao2]

Hello All,

I prepare a bunch of different workbooks every month some of which have pivot tables. My boss would like one workbook with all of these combined. I have the below macro which allows me to copy all of the tabs in whichever workbook I select. The problem is that even though I am coping the data that the pivot tables are using the data source still points to the old workbook instead of the new workbook. Is there any way to refresh the data sources on the pivot tables to point to the data within the new workbook?


Sub copy_sheets()

Dim wb1 As Workbook, wb2 As Workbook
Dim Ret1
Set wb1 = ActiveWorkbook
'Change the name of the destination workbook here

Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
, "Please select the file to import")
If Ret1 = False Then Exit Sub
Set wb2 = Workbooks.Open(Ret1)

For Each Sheet In wb2.Sheets
If Sheet.Visible = True Then
Sheet.Copy After:=wb1.Sheets(wb1.Sheets.Count)
End If
Next Sheet

wb2.Close SaveChanges:=False

Set wb2 = Nothing
Set wb1 = Nothing
End Sub



Thank you