Dynamically refreshing Pivot Table Range.

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

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

I've tried the Dynamic Range. It's adding columns, but it does not remove
columns. I will have situations where data is brought in dependant upon a
date range. If the date range returns fewer records than the prior run, the
range is not shrinking and blank rows are included. This then makes any
attempts at trying to do grouping within the Pivot table impossible.

Can the Dynamic Range shrink in size?

"Dave Peterson" wrote:

> 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
>

This formula taken from Debra Dalgleish's site:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

expects your data in column A to be contiguous--no blanks in column A.

The last 1 in the formula tells excel that the range is one column wide.

If you know the number of columns, you can change that 1 to what you need (say
13):
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),13)

Or you can count the number of cells that are filled in row 1 (again no gaps
allowed!):

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),Counta(sheet1!$1:$1))

===
If this doesn't help, you may want to post the formula you used.


Rob wrote:
>
> I've tried the Dynamic Range. It's adding columns, but it does not remove
> columns. I will have situations where data is brought in dependant upon a
> date range. If the date range returns fewer records than the prior run, the
> range is not shrinking and blank rows are included. This then makes any
> attempts at trying to do grouping within the Pivot table impossible.
>
> Can the Dynamic Range shrink in size?
>
> "Dave Peterson" wrote:
>
> > 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
> >

--

Dave Peterson