Change Data Source For Multiple Pivot Tables

POOR THREAD TITLE Please read THIS PAGE and THIS PAGE IN FULL

Im using MS Excel 2003, have multiple pivot tables of which the data source has changed. I have connected the pivot tables to an external database and it seems to be trying to refresh the same data for multiple pivots although it is the same data source. I know you are able to look at the same pivot table within the workbook by using the wizard and going back to select 'another pivottable report or pivot chart report' by looking at the first pivot table refreshed, however there are over 80 pivot tables and i was wondering if there was a quicker way this could be done through using VBA or even through the excel interface.

I have multiple pivot tables in one workbook and would like to change the
data source on all of the pivot tables. Is there a way to do this all at
once instead of selecting each table? The data is contained in another Excel
workbook. Thanks!

My goal is to loop through all the pivot tables in a workbook and update the source data range for each one. Some worksheets
have several pivot tables on them. The following code which I borrowed from various posts loops through the sheets but only
updates the first pivot table on each page. It also crashes occasionally with the error "A pivot table can't overwrite
another pivot table" on sheets that only have 1 pivot table on them. (I swear!)
If anyone could help me fix this so that it updates all the pivot tables on each worksheet I'd appreciate it very much.

VB:

AllWorkbookPivots()
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
ws.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'M:AMEX_CAN[qryAMEX_CA.xls]qryAMEX_CA'!$A$1:$BK$10000"
Next pt
Next ws
End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have used the same data for multiple pivot tables. The Data keeps on
changing and the rows gets added and deleted. So everytime I have to go
to all the pivot table and change the Datasource range.
Is there anymethod that can be used, which will automatically adjust
the datasource range for all the pivots without intervetion.

Hi,
I have a large CSV data file that I am using as an "external data source"
for a Pivot table in an (autoupdate) template.
That works fine. It's the sales file created automatically by an
application. So it's "sales.csv" and my template with the Pivot table is
"sales.xlt".

But I also have costs, so I have another file where costs are recorded
manually. The data is in "Costs.xls" and I will have another Pivot table
where I analyse the costs : "Costs.xlt"

Now I want to offset Costs against Sales, so I am looking for some way
to
get a new pivot table to read both the Sales.CSV and the Costs.XLS data and
combine the results.

Anyone here know how to do that ?
Or, can anyone point me to a site where that is explained.

Hi,
I have a large CSV data file that I am using as an "external data source"
for a Pivot table in an (autoupdate) template.
That works fine. It's the sales file created automatically by an
application. So it's "sales.csv" and my template with the Pivot table is
"sales.xlt".

But I also have costs, so I have another file where costs are recorded
manually. The data is in "Costs.xls" and I will have another Pivot table
where I analyse the costs : "Costs.xlt"

Now I want to offset Costs against Sales, so I am looking for
some way to
get a new pivot table to read both the Sales.CSV and the Costs.XLS data and
combine the results.

Anyone here know how to do that ?
Or, can anyone point me to a site where that is explained.

I have a worksheet with 70 small, simple pivot tables all referencing an OLAP
cube. I need to change the OLAP cube data source each month and do not want
to go to each pivot table and update the data source. How can I set up
something to allow me to change the data source once for all the pivot tables?

I have a page with 8 pivot tables looking back to the same source data. In Excel 2003 I used to be able to
change the data source for tables 2-8 to point to table 1 which reduced the file size quite a bit, but I can't see how to do
this in Excel 2010 - any ideas?!

Hello All,
I have multiple pivot tables in a worksheet that utilize the same data range. At times I need to update the
data range do to the fact that I am using the Grouping feature. Is there a way to update the data ranges for all the pivot
tables at once? Right now I need to update each pivot table separately.

I have a work workbook, with many sheets on it, i have a pivot running on one of the pages,but i want to change the data
source for the Pivot every month from one sheet to another,those sheets are named after the months. can somebody help me on
this???????

I am trying to change the data source for my pivot tables due to a mapped network drive change. I have tried to do the
wizard, back but it skips over the spot where you can change drive letters. Please assist.

I am trying to write a macro that changes the source data for multiple pivot tables on multiple sheets. here is what I have
so far. However it seems to only update 1 Pivot table per worksheet and crashes once it hits a sheet without any pivot
table. Any help is appreciated.

Sub AllWorkbookPivots()
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
ws.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"Data_063011"
Next pt
Next ws
End Sub

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'm using the same source data for multiple Pivot tables but when I get to
the 3rd one, the dialog box that Excel shows is too small to read the entire
names it generates so I can't tell which one I need to pick. The name starts
with the file name and the sheet name with the source range is so far to the
right it doesn't display. I have been unable to find a way to enlarge the
box or have it just display the sheet name and range instead of the whole
file name, etc. Has anyone else come across this and found a solution? I'm
working all in the same file, so the file name, which I can see, is useless
information. Thanks!

I want to create a spreadsheet that contaiins multiple pivot tables for a
single access database. As far as I see, Excel hardcodes the DSN information
for the ODBC connection. Thus if I give this speadsheet and access database
to someone else, they must put the access database in exactly the same
location (i.e. drive letter and subdirectory).

Normally, you would make a call for external data to the DSN which
contains
the pointer to the location of the database. Thus you can move the database
anywhere you want as long as you relfect those changes in one single
location, the DSN. Excel seems t hardcode the DSN name as well as the path to
the database thus not allowing you to change it location.

As a work around I had a thought to create queries to
use for each of the
pivot tables. I then tried creating the pivot tables based on a saved
queries. I figured then you can edit the individual query and change the
drive letters for all the info in the query (i.e. DSN, default location, etc)
since this all seems to be saved within the query. This would be labor
intensive but thought to be a workaround. However, when I saved the
spreadsheet with the pivot table. Disconnected the original mapped drive,
editited the query to reflect the new drive letter and tried to open up and
refresh the data, I got the error message telling me that it couldn't find
the ODBC datasource at the location of the original drive letter so it hard
coded again in there somewhere.

Anyone know of a workaround. Don't know if somethig could be done in VBA.

I need to have a series of custom tables, specifically formatted in MS
Excel.

I decided I'll make them based on Pivot Table as a source data and
Excel links with GETPIVOTDATA do deliver the data to the proper MS
Excel format worksheet template. After filling with dynamic data from
pivots the template is saved as workbook needed.

Each table consist of around 100 of GETPIVOTDATA links. Each
link has
it own pivot table in a 'working' worksheet. All the pivot tables are
based on the same external MS Excel file economizing on resources.
While making the pivot table I decided that the external data is
returned to Excel file.

I use VBA to manage thos Pivot tables. As a result the pages of the
pivot tables change delivering different results in Pivot table.

Unfortunately I have faced two difficult
situations:

1) Data that I use seems to remember with the quite 'statically'. Yes,
its true that when I update the MS Excel source file and give refresh
on the pivot table, the pivot refreshes. But if I copy the folder both
with source and report files the new, copied report file will still
source from the original source file. To change it, today I have to
manually change the source for each pivot table that is quite
time-consuming. Unfortunately I do not know how to make those links
more dynamic (always for example take from source.xls in the same
folder) or update the source automatically with VBA.

I'd like to make it automatically as manual work always
causes more
unexpected errors and its quite unefficient. Do you know how to solve
this?

2) When I change the pivot with VBA macro, the pivot itself changes,
but the links in custom formatted tables on other worksheet do not
'refresh'. I made a workaround by preparing special VBA procedure that
after VBA pivot table update, overwrites the links with identical
formulas in all cells. (Similar result to selecting the cell manually,
selecting the formula and pressing Enter). After that the links are
'refreshed' with the proper, chagned data.

This issue is done automatically, but maybe some of you know more
efficient / elegant solution?

I need to have a series of custom tables, specifically formatted in MS Excel.

I decided I'll
make them based on Pivot Table as a source data and Excel links with GETPIVOTDATA do deliver the data to the proper MS Excel
format worksheet template. After filling with dynamic data from pivots the template is saved as workbook needed.

Each table consist of around 100 of GETPIVOTDATA links. Each link has it own pivot table in a 'working' worksheet. All the
pivot tables are based on the same external MS Excel file economizing on resources. While making the pivot table I decided
that the external data is returned to Excel file.

I use VBA to manage thos Pivot tables. As a result the pages of
the pivot tables change delivering different results in Pivot table.

Unfortunately I have faced two difficult
situations:

1) Data that I use seems to remember with the quite 'statically'. Yes, its true that when I update the
MS Excel source file and give refresh on the pivot table, the pivot refreshes. But if I copy the folder both with source and
report files the new, copied report file will still source from the original source file. To change it, today I have to
manually change the source for each pivot table that is quite time-consuming. Unfortunately I do not know how to make those
links more dynamic (always for example take from source.xls in the same folder) or update the source automatically with
VBA.

I'd like to make it automatically as manual work always causes more unexpected errors and its quite
unefficient. Do you know how to solve this?

2) When I change the pivot with VBA macro, the pivot itself changes,
but the links in custom formatted tables on other worksheet do not 'refresh'. I made a workaround by preparing special VBA
procedure that after VBA pivot table update, overwrites the links with identical formulas in all cells. (Similar result to
selecting the cell manually, selecting the formula and pressing Enter). After that the links are 'refreshed' with the proper,
chagned data.

This issue is done automatically, but maybe some of you know more efficient / elegant solution?

Hi, could anyone advise how to automatically change the external data source for a pivot table according to the username of
the person logged in to Windows?

I need to use two separate excel files: one containing the pivot table and a
separate file containing the source data. Both files will exist in the user's My Documents folder. I need to distribute the
pivot table file to multiple users. When they open the pivot table file, the data source needs to be their own "...My
Documentssource data.xls" How can this be achieved?