1

I have the following problem that I have trouble solving (couldn't find a case online that quiet fits what I have in mind).

I currently update a normal Excel table manually with data from a Pivot table, which is updated once a week (it's basically the exact same table as the Pivot, only as a normal table with values). I want the data displayed in a normal Excel table as values for calculation purposes.

As the manual updates of the normal tables are currently pretty time-consuming, I was wondering if there was a way to automatically refresh the table as soon as the Pivot table is refreshed with new data?

Normally, I would simply link the data from the Pivot to the according cells in the normal table, but the size of the Pivot table changes weekly depending on the Input data.

Is there a way to adjust the normal table in a way that it also changes ist size and exactly mirrors the Pivot?

I hope I made myself clear.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Fes174
  • 11
  • 2

2 Answers2

0

try to use the Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Sub to track the pivot table update and then you can run some code to copy the content and paste in the other table, finally you can use this command "ActiveSheet.ListObjects("Table1").Resize Range("$A$1:$C$7")" to change the size.

Vinicius B
  • 162
  • 9
0

See my answer at this other stackoverflow thread that does this.

It resizes a Table to match the dimension of a PivotTable. You can then reference the PivotTable cells you want from that Table using calculated columns.

Also check out my guest post here that creates dynamic named ranges for PivotTables, which is another way to reference any part of a PivotTable.

Community
  • 1
  • 1
jeffreyweir
  • 4,668
  • 1
  • 16
  • 27