Solved

How to resize range used by pivot table?

Posted on 2014-03-24
4
1,971 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
  • 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

772 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