?
Solved

How to resize range used by pivot table?

Posted on 2014-03-24
4
Medium Priority
?
2,487 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

765 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