CODEWITHSUNDEEP
vbaexcel
user10171431
user10171431

Reputation: 61

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

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.

Upvotes: 2

Views: 1513

Answers (1)

L42
L42

Reputation: 19727

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.

Upvotes: 1

Related Questions