Problem with Pivots

That shouldn't happen unless you update the data by actually deleting rows rather than clearing them.

In any event you would be better off using a dynamic named range or a Table which will both expand and contract as required. See Debra's page here for dynamic named ranges: http://www.contextures.com/xlPivot01.html

and note the link to this page for creating a Table: http://www.contextures.com/xlExcelTable01.html

If you have 2007 or later I would recommend a Table.

Hi Rory,

I am using a table.  ='RawData'!$A$7:$AL$10000

So the Table is called RawData and the range of the table is A7:AL10000.

But when new data is pasted onto the Raw Data sheet the range changes. ????

If you are in fact using a Table then you don't enter the range address in the Source Data for the pivot, you just enter the table name = RawData, and then you should only have to refresh the pivot after updating the data. If that's not working, then I don't think you are using a Table.

Hi Rory,

I am using a Table but somthing is wrong.

I set up the Table in Excel 2010 through 'Insert/Table' The range is $A$7:$AL$10000

I called the table RawData and this is confirmed when I go into 'Design'

In the Pivot in Table/Range I have entered RawData

But, when I bring in a new file and paste this into the worksheet the Table Range has changed from 10000 to 1242 which is the total number of rows that there were yesterday. That's fine but when I bring in today's file which has 4000 rows the table does not adjust it remains at 1242

Appreciate your help with this.

How exactly do you bring in today's file with the extra rows - copy and paste?

yes copy and Paste

You don't delete anything first? How do you do it when the range is smaller than the current table?

I copy the range A:AL

In Excel options, in the Advanced section, do you have the setting for 'extend data range formats and formulas' checked?

Also, does your range still appear as a Table when you paste over the top of it (either less or more data)?

Hi Rory,

In Excel options, in the Advanced section, do you have the setting for 'extend data range formats and formulas' checked?

Yes

Also, does your range still appear as a Table when you paste over the top of it (either less or more data)?  

It does but if I have brought in less data the previous day the Table range adjusts to the new lower range and when I bring in more data the next day it remains at the lower range.

i.e. origianal range A7:AL10000

less data changes to A7:AL4963

more data remains at A7:AL4963 despite there being 7300 rows

I confess I'm confused. If I have a table that starts in row 7, and I copy and paste entire columns from another sheet, the table is removed.

so I guess the answer is to build into my macro the Range each time

Rory

it looks like that works.

Thanks