Change the range of a pivot table data source

Hi
Hope someone can help with this. I want to load a raw data file each month
into a template workbook. The size of the raw data changes so I want to
change the source range for each pivot table.

How do I determine where the raw data ends (rows and columns) and tell the
pivottable data source to use the new reference?

Any help you can provide would be fantastic.
--
Anthony White

You could use a dynamic range as the pivot source, and refer to the
range name in the code. There are instructions here:

http://www.contextures.com/xlPivot01.html


Tony White wrote:
> Hi
> Hope someone can help with this. I want to load a raw data file each month
> into a template workbook. The size of the raw data changes so I want to
> change the source range for each pivot table.
>
> How do I determine where the raw data ends (rows and columns) and tell the
> pivottable data source to use the new reference?
>
> Any help you can provide would be fantastic.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Ok. My gameplan is to first import the csv file, then using vba name the
range of data using the OFFSET function( is there documentation on this and
the SUBTOTALS functions?), next using vba change the source of the pivot
table.

Thanks for your help. I have been to your site before. It is great!

"Debra Dalgleish" wrote:

> You could use a dynamic range as the pivot source, and refer to the
> range name in the code. There are instructions here:
>
> http://www.contextures.com/xlPivot01.html
>
>
> Tony White wrote:
> > Hi
> > Hope someone can help with this. I want to load a raw data file each month
> > into a template workbook. The size of the raw data changes so I want to
> > change the source range for each pivot table.
> >
> > How do I determine where the raw data ends (rows and columns) and tell the
> > pivottable data source to use the new reference?
> >
> > Any help you can provide would be fantastic.
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

Thanks for letting me know that you like my site!

There's information on OFFSET and SUBTOTAL in Excel's Help, and you can
download Peter Nonely's function workbook from Ron de Bruin's site:

http://www.rondebruin.nl/id.htm

Tony White wrote:
> Ok. My gameplan is to first import the csv file, then using vba name the
> range of data using the OFFSET function( is there documentation on this and
> the SUBTOTALS functions?), next using vba change the source of the pivot
> table.
>
> Thanks for your help. I have been to your site before. It is great!
>
> "Debra Dalgleish" wrote:
>
>
>>You could use a dynamic range as the pivot source, and refer to the
>>range name in the code. There are instructions here:
>>
>> http://www.contextures.com/xlPivot01.html
>>
>>
>>Tony White wrote:
>>
>>>Hi
>>>Hope someone can help with this. I want to load a raw data file each month
>>>into a template workbook. The size of the raw data changes so I want to
>>>change the source range for each pivot table.
>>>
>>>How do I determine where the raw data ends (rows and columns) and tell the
>>>pivottable data source to use the new reference?
>>>
>>>Any help you can provide would be fantastic.
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html