Free Microsoft Excel 2013 Quick Reference

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.


Post your answer or comment

comments powered by Disqus
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


Hi,

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.

Please educate.

Thanks & Regards
Anand

--
anandmr65
------------------------------------------------------------------------
anandmr65's Profile: http://www.excelforum.com/member.php...o&userid=30728
View this thread: http://www.excelforum.com/showthread...hreadid=559203

Hi,

I need to change the data range for a pivot tables in excel 2007. In 2003 there was the option to do so in the wizard but I cant find that option in 2007. Any clues?

Thanks

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.

TIA

Erasmus

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.

TIA

Erasmus

Hi,

I currently have a macro button which refreshes the pivot tables in my
report.
The data source for the pivots is a db which sits in a folder which is
no longer accessible for users to view.

I therefore have a copy of the db in a shared area. I need to relink
my pivot tables to the new db source (path).

Is there anyway of changing the source without completely re-doing all
of the pivots?

Many Thanks in Advance.
Jason

--
jjj
------------------------------------------------------------------------
jjj's Profile: http://www.excelforum.com/member.php...fo&userid=7424
View this thread: http://www.excelforum.com/showthread...hreadid=532551

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?

Hi,

I currently have a macro button which refreshes the pivot tables in my report.
The data source for the pivots is a db which sits in a folder which is no longer accessible for users to view.

I therefore have a copy of the db in a shared area. I need to relink my pivot tables to the new db source (path).

Is there anyway of changing the source without completely re-doing all of the pivots?

Many Thanks in Advance.
Jason

Hi,

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

Thanks
Mark

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.

Thanks

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

Can you use more than 1 worksheet as the source for a pivot table?
I saw where I could use multple consolidations, but I don't think
consolidation will work for me. I need to count records by month.

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 have an actual sales data source, and budget sales data source, i need to
compare actual vs budget years, is there a way to merge two data source into
one pivot table?

Has anyone had this problem with Excel 2007 crashing when trying to up date a data source within a pivot table?

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!

Hi,

We are using several seperate raw data csv .txt files as source for multiple pivot tables with information on for example customer backorder, stock levels, sales QTY, forecast.

Is there a possibility to combine the .txt files into one range and use as a source for a pivot table that can give overview of all the above data?

all suggestions are welcome

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.

Hi,

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?

My office is 2003 Professional, my system is WinXP.

Thanks,

Michal

--
mbobro
------------------------------------------------------------------------
mbobro's Profile: http://www.excelforum.com/member.php...fo&userid=1996
View this thread: http://www.excelforum.com/showthread...hreadid=559596

Hi,

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?

My office is 2003 Professional, my system is WinXP.

Thanks,

Michal

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?

Many thanks in advance,

Jack.


No luck finding an answer? You could always try Google.