Easy way to change Pivot table source files?


J

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
 
Ad

Advertisements

R

Roger Govier

Hi Jesper

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

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
 
B

Bill R

Jesper Audi said:
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
 
Ad

Advertisements

J

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top