Updating pivot table data source after copying from one workbook to another

Question

I have a macro that copies a pivot table and its original source data in Workbook A to Workbook B. Is there a way to update the source data via Excel VBA that takes information from Workbook B ("fields") instead of Workbook A?

This is the code for identifying workbook B:

Dim WrkbookB As Workbook
Set WrkbookB = ActiveWorkbook

This is the code for importing the pivot table (in Sheet1) and original source data ("Fields") in Workbook A:

Dim WrkbookA As Workbook
folder = Application.GetOpenFilename("Excel.xlsx (*.xlsx), *.xlsx," & _
        "Excel.xls (*xls), *xls", 1, "Select Workbook A")
Set WrkbookA= Workbooks.Open(Filename:=folder)
WrkbookA.Sheets("Sheet1").Copy After:=WrkbookB.Sheets(1)
ActiveSheet.Name = "Sheet1"
WrkbookA.Sheets("Fields").Copy After:=WrkbookB.Sheets(1)
WrkbookA.Close SaveChanges:=False

Any help on how to change the data source is greatly appreciated.

A good suggestion is to avoid using any Active properties and stick to either specifying workbooks by name and worksheets by name or index number — Marcucciboy2

L42 L42 · Accepted Answer · 2018-08-20T22:47:21

This is assuming you have successfully imported the sheets.
What you need is to change the pivot cache and we have a method to do that.
Before that, you need to identify the source range of the data:

Dim sourceRange As String
'/* this is a short cut but you can improve this, dynamically getting your source */
sourceRange = WrkbookB.Sheets("Sheet1").UsedRange.Address(, , xlR1C1, True)

Once you have the source, you need to create the cache:

With Sheets("Field") '/* this is in Workbook B */
  .PivotTables(1).ChangePivotCache WrkbookB. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:=sourceRange)
End With

I used PivotTables(1) assuming you only have 1 pivot table in Fields sheet.
If not, you will have to replace it with the target pivot table name.

Updating pivot table data source after copying from one workbook to another

1 Answers