I have many summary worksheets that provide data to many pivot tables all are
in the same workbook. "Summary1" provides data to "PivotTable1",
"PivotTable2", and "PivotTable3". "Summary2" provides data to "PivotTable3"
and so on. My summary worksheets get updated weekly and so are variable in
the number of rows from week to week. The columns are somewhat more stable
but could eventually change over time. I would like my pivot tables to be
able to pick up all the data in my summary worksheets without me having to
rerange by hand. I have tried using the named range OFFSET formula solution
and it typically works just fine under normally situations, but because I'm
updating the current summary worksheets via an Access export to scrammbles up
the name range reference and so I'm looking for more of a programmatic
solution that I can apply to multiple worksheets and pivot tables. Any help
would be really appreciated. Thanks.

Are you exporting a query from Access? If so, a name may be created
automatically, in the Excel file. Check in InsertNameDefine, and use
that name, if one exists.

Mhughes wrote:
I have many summary worksheets that provide data to many pivot tables all are
in the same workbook. "Summary1" provides data to "PivotTable1",
"PivotTable2", and "PivotTable3". "Summary2" provides data to "PivotTable3"
and so on. My summary worksheets get updated weekly and so are variable in
the number of rows from week to week. The columns are somewhat more stable
but could eventually change over time. I would like my pivot tables to be
able to pick up all the data in my summary worksheets without me having to
rerange by hand. I have tried using the named range OFFSET formula solution
and it typically works just fine under normally situations, but because I'm
updating the current summary worksheets via an Access export to scrammbles up
the name range reference and so I'm looking for more of a programmatic
solution that I can apply to multiple worksheets and pivot tables. Any help
would be really appreciated. Thanks.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I went back and followed your direction and it works nicely. Thanks for the
quick response it was most helpful.

"Debra Dalgleish" wrote:

Are you exporting a query from Access? If so, a name may be created
automatically, in the Excel file. Check in InsertNameDefine, and use
that name, if one exists.

Mhughes wrote:
I have many summary worksheets that provide data to many pivot tables all are
in the same workbook. "Summary1" provides data to "PivotTable1",
"PivotTable2", and "PivotTable3". "Summary2" provides data to "PivotTable3"
and so on. My summary worksheets get updated weekly and so are variable in
the number of rows from week to week. The columns are somewhat more stable
but could eventually change over time. I would like my pivot tables to be
able to pick up all the data in my summary worksheets without me having to
rerange by hand. I have tried using the named range OFFSET formula solution
and it typically works just fine under normally situations, but because I'm
updating the current summary worksheets via an Access export to scrammbles up
the name range reference and so I'm looking for more of a programmatic
solution that I can apply to multiple worksheets and pivot tables. Any help
would be really appreciated. Thanks.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html