I have a macro that collects lots of data from four sheets and re-arranges it
so it is suitable for a pivot table. I have the pivot table, but I want to
make sure if my data grows that the new rows are picked up by the pivot
table. How can I set the data range used by the pivot table from my macro?

After the macro updates the data, have it create a name:

rngData.Name = "'" & wsData & "'!PivotData"

where rngData is the entire data range including the header row and wsData
is the worksheet containing this data. Select the Pivot Table, then open the
Pivot Table wizard, go Back, and for source data, enter the
worksheet-qualified name in the box for the source data.

Now when the macro updates the data range, it updates the name. Then when
the pivot table is refreshed, it uses the new larger data range.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Graham Y" wrote in message
...
I have a macro that collects lots of data from four sheets and re-arranges
it
so it is suitable for a pivot table. I have the pivot table, but I want to
make sure if my data grows that the new rows are picked up by the pivot
table. How can I set the data range used by the pivot table from my macro?