I need to change the external data source for pivots found on multiple
worksheets within the same workbook. Is there code that I could use to do
this in one click of a button.

Also, how do keep the number formatting for my pivots after I refresh them?
I tried just going to table options and selecting "preserve formatting" and
unselecting "autoformat table" but this doesn't maintain the number
formatting inside the pivot.

Hi,

If you have multiple pivot table on a Worksheet, you have to refresh each
table individually, because the source file is different for each pivot table.

Challa Prabhu

"Pivot Tables" wrote:

I need to change the external data source for pivots found on multiple
worksheets within the same workbook. Is there code that I could use to do
this in one click of a button.

Also, how do keep the number formatting for my pivots after I refresh them?
I tried just going to table options and selecting "preserve formatting" and
unselecting "autoformat table" but this doesn't maintain the number
formatting inside the pivot.

I have one pivot table per worksheet and about 30 worksheets in the workbook.
Each pivot is pulling from the same external data file (excel file). Is
there a way to change the external data source for all pivots at the same
time?

"challa prabhu" wrote:

Hi,

If you have multiple pivot table on a Worksheet, you have to refresh each
table individually, because the source file is different for each pivot table.

Challa Prabhu

"Pivot Tables" wrote:

I need to change the external data source for pivots found on multiple
worksheets within the same workbook. Is there code that I could use to do
this in one click of a button.

Also, how do keep the number formatting for my pivots after I refresh them?
I tried just going to table options and selecting "preserve formatting" and
unselecting "autoformat table" but this doesn't maintain the number
formatting inside the pivot.

Hi,

Select different source data for a PivotTable or PivotChart report

If you returned data from an Office Data Connection file (.odc extension)
directly to a PivotTable report, or your report is based on OLAP (OLAP: A
database technology that has been optimized for querying and reporting,
instead of processing transactions. OLAP data is organized hierarchically and
stored in cubes instead of tables.) source data, you cannot use different
source data in the report.

1. Click the PivotTable report (PivotTable report: An interactive,
crosstabulated Excel report that summarizes and analyzes data, such as
database records, from various sources, including ones that are external to
Excel.). For a PivotChart report (PivotChart report: A chart that provides
interactive analysis of data, like a PivotTable report. You can change views
of data, see different levels of detail, or reorganize the chart layout by
dragging fields and by showing or hiding items in fields.), click the
associated PivotTable report (associated PivotTable report: The PivotTable
report that supplies the source data to the PivotChart report. It is created
automatically when you create a new PivotChart report. When you change the
layout of either report, the other also changes.).

2. On the Data menu, click PivotTable and PivotChart Report

3. In step 3 of the wizard, click Back.

If the Back button is unavailable, your report is based on OLAP data, and
you cannot change it to use different source data. If you want a report based
on a different OLAP cube (cube: An OLAP data structure. A cube contains
dimensions, like Country/Region/City, and data fields, like Sales Amount.
Dimensions organize types of data into hierarchies with levels of detail, and
data fields measure quantities.), create a new report.

4. In step 2 of the wizard, notice whether the report is based on an
Microsoft Excel range (the step has an edit box labeled Range:) or external
data (the step has a Get Data button), and then do one of the following:

A. Specify a different Excel range
B. Specify different external data

5. Click Finish.

6. Drag any new fields that you want to display from the PivotTable Field
List window onto the report.

Note After you make this change to a PivotChart report or its associated
PivotTable report, some chart formatting may be lost.

Challa Prabhu

"Bonnie" wrote:

I have one pivot table per worksheet and about 30 worksheets in the workbook.
Each pivot is pulling from the same external data file (excel file). Is
there a way to change the external data source for all pivots at the same
time?

"challa prabhu" wrote:

Hi,

If you have multiple pivot table on a Worksheet, you have to refresh each
table individually, because the source file is different for each pivot table.

Challa Prabhu

"Pivot Tables" wrote:

I need to change the external data source for pivots found on multiple
worksheets within the same workbook. Is there code that I could use to do
this in one click of a button.

Also, how do keep the number formatting for my pivots after I refresh them?
I tried just going to table options and selecting "preserve formatting" and
unselecting "autoformat table" but this doesn't maintain the number
formatting inside the pivot.

I think I'm not communicating my problem correctly. I want to record a macro
that would automatically update the external source data I have on all of my
pivot tables within the same workbook. So...if I change the external source
data for one pivot table...I want all the other pivot tables to automatically
select that same source data.

"challa prabhu" wrote:

Hi,

Select different source data for a PivotTable or PivotChart report

If you returned data from an Office Data Connection file (.odc extension)
directly to a PivotTable report, or your report is based on OLAP (OLAP: A
database technology that has been optimized for querying and reporting,
instead of processing transactions. OLAP data is organized hierarchically and
stored in cubes instead of tables.) source data, you cannot use different
source data in the report.

1. Click the PivotTable report (PivotTable report: An interactive,
crosstabulated Excel report that summarizes and analyzes data, such as
database records, from various sources, including ones that are external to
Excel.). For a PivotChart report (PivotChart report: A chart that provides
interactive analysis of data, like a PivotTable report. You can change views
of data, see different levels of detail, or reorganize the chart layout by
dragging fields and by showing or hiding items in fields.), click the
associated PivotTable report (associated PivotTable report: The PivotTable
report that supplies the source data to the PivotChart report. It is created
automatically when you create a new PivotChart report. When you change the
layout of either report, the other also changes.).

2. On the Data menu, click PivotTable and PivotChart Report

3. In step 3 of the wizard, click Back.

If the Back button is unavailable, your report is based on OLAP data, and
you cannot change it to use different source data. If you want a report based
on a different OLAP cube (cube: An OLAP data structure. A cube contains
dimensions, like Country/Region/City, and data fields, like Sales Amount.
Dimensions organize types of data into hierarchies with levels of detail, and
data fields measure quantities.), create a new report.

4. In step 2 of the wizard, notice whether the report is based on an
Microsoft Excel range (the step has an edit box labeled Range:) or external
data (the step has a Get Data button), and then do one of the following:

A. Specify a different Excel range
B. Specify different external data

5. Click Finish.

6. Drag any new fields that you want to display from the PivotTable Field
List window onto the report.

Note After you make this change to a PivotChart report or its associated
PivotTable report, some chart formatting may be lost.

Challa Prabhu

"Bonnie" wrote:

I have one pivot table per worksheet and about 30 worksheets in the workbook.
Each pivot is pulling from the same external data file (excel file). Is
there a way to change the external data source for all pivots at the same
time?

"challa prabhu" wrote:

Hi,

If you have multiple pivot table on a Worksheet, you have to refresh each
table individually, because the source file is different for each pivot table.

Challa Prabhu

"Pivot Tables" wrote:

I need to change the external data source for pivots found on multiple
worksheets within the same workbook. Is there code that I could use to do
this in one click of a button.

Also, how do keep the number formatting for my pivots after I refresh them?
I tried just going to table options and selecting "preserve formatting" and
unselecting "autoformat table" but this doesn't maintain the number
formatting inside the pivot.