Dynamically Change a Pivot Table's Data Range

[Guest]

I have a workbook with a several pivot tables all based on the data contained
within one sheet of the workbook. Attached to this workbook, i have a macro
that will update the data sheet (the data is pulled from an oracle database).

Within the same macro, i would like to change the range of data each pivot
table is based on because every time the data sheet is updated, more rows of
data are pulled then the last time (i.e. the pivot table was based on
Range("A1:N50") but should now be based on Range("A1:N75"). However, i
cannot seem to figure out how to change this programmatically. Can anybody
help?

 

[Jon Peltier]

Dan -

Create a dynamic name. Go to Insert menu > Names > Define. In the Name box
enter PTSource (or suitable name), and in the Refers To box enter this
formula:

=OFFSET(Sheet1!$A1:$N1,0,0,COUNTA(Sheet1!$A:$A),)

This counts the number of cells in column A which contain entries, so ensure
there are no blanks.

Now in the PT wizard, enter Sheet1!PTSource as the data range.

- Jon

 

[Guest]

Jon,

I appreciate your quick response. However, I am looking to do this in an
automated fashion. In (likely quite poor) pseudo-code, this is the sort of
thing i am looking to do:

'update the data-sheet
'by dumping the results of an sql query into the sheet

'go to the first sheet containing a pivot table
Application.Worksheets("By Brand").Activate
ActiveSheet.PivotTables(1).UpdateDataRange("DataSheet!A1ataSheet!N" &
countOfRecords)

'go to the next sheet containing a pivot table and do the same thing

Is this something that is possible?

 

[Jon Peltier]

After you set up the dynamic name, it's completely automatic, without the
need for code.

- Jon

 

[Guest]

That worked... thanks Jon!

After you set up the dynamic name, it's completely automatic, without the
need for code.

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