asked on

How to resize range used by pivot table?

Hello - I've got a pivot table that is based on a range of data built up on one tab from a series of copy/paste operations from another tab. The final range will be larger (more rows) every time the operation is run.

How can I resize the range in the pivot table data source after the copy/paste's?

Thanks

nutsch

One easy and unsatisfactory way: change the datasource and include the additional rows.

Two better ways, one easy, one more complex:

Easy one: Turn your data into a table / listobject with Ctrl+T. Summarize your table with a pivot table and everytime you add rows, the table should pick them up and you just have to refresh the pivot.

Harder one: Define a dynamic named range that will expand as you add rows and use it to define your pivot. Formula for such a range could be =OFFSET(A1,0,0,counta(A:A),5) for a range with 5 columns and as many rows as column A has values.

Thomas

mlagrange

ASKER

I'm sorry - I should have said that I was trying to do this in VBA.
But you did get me started; I created a small range on the target tab:
MyTargetTab!$A$1:$C$2

I kicked off the copy/paste process:
Sheets("MyTargetTab").Select
Range("L1Table").ClearContents

and ran to the point after it pasted in the first set of data (~200 rows). The range stayed at $A$1:$C$2; it did not expand to encompass the pasted data.

I was thinking the $'s was what made a range dynamic

Do you have a VBA solution?

Thanks for your response

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mlagrange

ASKER

Works like a charm! Thanks!