Solved

How to resize range used by pivot table?

Posted on 2014-03-24
4
2,206 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

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…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

738 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