Maybe you can use a dynamic range name

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic

Rob wrote:

I have a Pivot Table which is populated by a worksheet which itself is
populated via a SQL Server Stored Proc, using OLE DB. I get the RowCount of
the worksheet that has the data read into it, and use it for the Upper Row
limit of the Pivot Table Range, using the following code:

Sheets("Projected Shipments").PivotTableWizard SourceType:=xlDatabase,
SourceData:= _
"Sheet1!R1C1:R" & dRowCount & "C29"

Where dRowCount is my 'dynamic' number of rows returned by the SQL
Statement. The problem is after my processes run, the Upper range for the
Pivot table data is not the dynamic value I supply above.

How can I get it so that I can dynamically and correctly reset the
boundaries (rows) for the PivotTable cache to get it to include all of my
rows?

Thank you

--

Dave Peterson