Easy way to change Pivot table source files?

[Jesper Audi]

Is there an easy way in Excel 2007 to rename the location of pivot tables
(something along the lines of what you can do within edit links).

I have about a dozen files each containing about 10 pivot tables that
reference a data file outside the workbook. When copying the whole folder
structure so that I could use the same spreasheets with different data, the
pivot reference still realtes to the old datafile.

I thought the easiest way might be to open my workbook that has the pivot
tables and at the same time the source datafile workbook and re-name the
latter it to a different location. However that doesn't seem to work.

Any ideas much appreciated.

(I do recall in previous versions of excel, if each pivot table referenced
the same data, there was some way to get to that data and change it where
necessary. With my current problem, all pivots are referencing the same
data, so it would be nice if I could just make the change once)

Thanks
Jesper Audi

I need to change the loca

 

[Roger Govier]

Hi Jesper

Click on any cell within your PT>Options tab>Data section>Change Data Source

 

[Jesper Audi]

Hi Roger

Sorry but I want to avoid having to do that upwards of 10 times in each
workbook.

Do bear in mind that nearly all the pivot tables are the same source and I
would like to change it just once as you would be able to do if I had
hundreds of links referring to an external workbook.

Thanks for your interest

Jesper

 

[Bill R]

Hi Roger

Sorry but I want to avoid having to do that upwards of 10 times in each
workbook.

Do bear in mind that nearly all the pivot tables are the same source and I
would like to change it just once as you would be able to do if I had
hundreds of links referring to an external workbook.

Thanks for your interest

Jesper

Most (if not all) tasks that can be done manually can be done in a Macro.
Once written and working it will always work and consistently and is ideal
for repetative tasks.

Bill R

 

[Roger Govier]

Hi Jesper

I had not noticed that you PT's were based on external data.
Ron Coderre has an excellent Add in for dealing with editing connection
strings.
http://www.contextures.com/xlPivotPlayPLUS01.html

 

[Jesper Audi]

Sorry when I said external, I simply meant in another workbook on my files.

I did go to contextures and found and downloaded the add-in but as you say
this is more for external data connections.

I wish I could do a macro to help, but I have about 10 sheets in each
workbook and several pivot table on different sheets which are in different
positions.

What I really want is somewhere where the pivot table ranges are defined.
If in this case there was only one pivot table range then I should only have
to change the range once.

I think I shall just have to cut and paste the range into each of the pivot
table's source data.

Thanks for your help anyway

Jesper Audi