Refreshing a Pivot Table from a dynamic range

[matpj]

Hi there,
I have a pivot table on one worksheet, that is refreshed from a rang
of data on a second worksheet.

the data is refreshed using a query, so can contain different number
of rows.

how can I code it to always refresh using the complete range from th
data worksheet?

can anyone help

 

[Tom Ogilvy]

Is there a defined name for the table, something like External_Range1 or
something close to that.

Use that name as the source for you pivot table.

 

[Guest]

If you use a named range (Insert --> Name --> Define) and under 'Refers to'
use the offset formula to define a range (eg:
=OFFSET(Data!$A$1,0,0,MAX(2,COUNTA(Data!$A:$A)),11) - the max statement
ensures that if your data range returns no data, the pivot table will still
work. Look under Help for details of how to use the offset function properly)

Then use the named range as your pivot table source data.

This assumes that you are using formulas at the side of the query to process
the data further. If all of your data is in the query, then you can use the
name of the query as the data range for the pivot table.

To have everything automatically update when you refresh the query, you need
to disable the background refresh in the query (under data range properties
--> Refresh control) , and in table options on the pivot table, check the box
by 'refresh on open'

I hope that helps!

Richard