Macro to extend a pivot table range

[Phil Platt]

Evening all!

I have a worksheet that uses an ODBC link to create a table. The table
gets longer each time it's refreshed, but it's width stays the same.

I also have a pivot table based on this worksheet.

What I need is a macro that updates my pivot table to include the
extended range of the table.

I've tried naming the input range, then using a macro to update the
extent of the range and using that to update the pivot, but it never
works. If I look at the new range, the "refers to" panel always says
=TRUE - which is meaningless to me!

What I need is a routine that updates a named range called
"sourcedata" on a worksheet called "Input", and feeds it back to a
pivot table on a worksheet called "Analysis".

The data on the "Input" worksheet will always start in A8, and never
extends past column Y. However the number of rows increases every time
the ODBC link is refreshed.

Any help is very gratefully received!!!

Thanks,

Phil

 

[Debra Dalgleish]

You can use a dynamic range as the source for the pivot table. There are
instructions here:

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

 

[Vaughan]

You can create a pivot table based directly on an ODBC query (at least you can in XL2000). Just select External Data Source in the first dialog box that the wizard presents.

 

[Phil Platt]

That works perfectly Debra - thanks!

You can use a dynamic range as the source for the pivot table. There are
instructions here:

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