Spy Journal 3.0 Filtering the web for you

I had a colleague ask me this question today. (edited to protect the innocent – you know who you are!)

I copy data out of E_____ (am sure you have heard of it), and paste it into an excel spreadsheet.  I have a couple of pivot tables hanging off the back of this data.  As this is project data, the longer the project goes on, the larger the dataset gets.  Is there any way in VBA that I can get the pivot table to auto-expand the data set that it is using? 

Here is my answer.

Hey W__

There is a simple solution that doesn’t require any vba at all

First you will need to make a dynamic range name that covers the data being pasted.

Basically this uses volatile functions to calculate the number of rows of data when you paste the data in.

Then the second thing you do is change your pivot tables source data to read from the range name not a cell reference.

Ok how to make a dynamic range name.

Press CTRL F3 or go to Edit Insert Name Define

Name your range – e.g. pivot_table_data1

In the refers to section type or copy and paste this formulas

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

Assuming that your data starts in cell A1 of Sheet1. Edit accordingly before pasting in

What this formula does is creates an array that starts at cell a1 goes zero cells down and zero cells right, and is x cells deep and y cells wide. x = the count of all non blank cells in column A and y the count of all non blank cells in row 1. Assumes that column A and row 1 are contiguous data blocks the same length and width as the whole data set. If not use columns and rows that are.

Now in your pivot table right click and go to Pivot Table Wizard

Hit Back and go to the range selection

Type = and the range name = e.g. =pivot_table_data1

In my case my range name was called test.

Hit Finish and it is done.

Now whenever you paste more data into the data sheet the pivot table just needs to be refreshed to pick it up

Click anywhere on the pivot table and click the exclamation mark in the toolbar.

The instructions above assume you are using office 2003. In office 2007 the Name Manager is a little more confusing, but can be accessed with the same keyboard shortcut and you will need to create a new range name and then use the same formula listed above.

Cheers

Tim