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?

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Dan Thorman" wrote in message
...
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,

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("DataSh eet!A1:DataSheet!N" &
countOfRecords)

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

Is this something that is possible?

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Dan Thorman" wrote in message
...
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?

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
_______


"Dan Thorman" wrote in message
...
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("DataSh eet!A1:DataSheet!N" &
countOfRecords)

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

Is this something that is possible?

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Dan Thorman" wrote in message
...
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?

That worked... thanks Jon!

"Jon Peltier" wrote:

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
_______


"Dan Thorman" wrote in message
...
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("DataSh eet!A1:DataSheet!N" &
countOfRecords)

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

Is this something that is possible?

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Dan Thorman" wrote in message
...
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?