Solved

How to resize range used by pivot table?

Posted on 2014-03-24
4
2,153 Views
Last Modified: 2014-03-24
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
0
Comment
Question by:mlagrange
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39951338
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
0
 

Author Comment

by:mlagrange
ID: 39951470
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
0
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 39951489
in VBA, something like this would work, with sheets called Data and Pivot

Dim lLastRow As Long

lLastRow = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row

Sheets("Pivot").PivotTables(1).ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        ThisWorkbook.Sheets("Data").Range("A1:C" & lLastRow), Version:=xlPivotTableVersion14)

Open in new window

0
 

Author Closing Comment

by:mlagrange
ID: 39951857
Works like a charm! Thanks!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question