CODEWITHSUNDEEP
vbaexcelpivotvlookup
Python learner 93
Python learner 93

Reputation: 105

How would I use VBA to automatically populate an excel worksheet with data from the pivot table of a separate excel workbook?

I have two open Excel workbooks. One has some data in a pivot table, like so:

Row Labels   Date
A             5

B             4

C             3

The other, separate workbook is blank other than Column A, which lists the same Row Labels as are in the pivot table, but possibly in a different order:

Row Labels

B

A

C

I would like to place a button on the second workbook that, when pushed, will automatically populate the row labels with the appropriate columns from the pivot table, in the correct order.

I know that VLOOKUP will be involved somehow, but I am not quite sure how to get there. Ideally, it would be nice if this VBA code could be dynamic, so that the button would work no matter the size of the pivot table, and no matter how many row labels will be in the second workbook.

Upvotes: 1

Views: 342

Answers (1)

Olly
Olly

Reputation: 7891

Use the GETPIVOTDATA function - for example:

    =GETPIVOTDATA("Date",[PivotWorkbook]Sheet1!$A$1,"Row Field",$A2)

The workbook containing the Pivot Table must be open to calculate the result.

Upvotes: 2

Related Questions