transfer pivot table to new workbook data

[wayshower]

copy pivot table to new workbook
Question Reply Quote Edit

Forgive me if this is a simple question.
I create a new work book each year for financial information. I have a set
of pivot tables on one year, say 2006-7 and wish to maintain consistency for
future years.
How can I transfer the pivot tables for 2006-7 (in the 2006-7 workbook) to
accept source data from 2007-8 workbook, then 2008-9 work book etc. so my
comparison data is in identical layout etc (for both tables and charts)? Or
do I have to create the pivot tables separately from scratch, year to year?
I am using MS Office 2007.

Help most appreciated.
wayshower.

 

[Dave Peterson]

I'd try this:

Use windows explorer, copy the old workbook to a new name.
Then delete most (all?) of the data from the range the pivottable uses.
(Or keep a few, but change the info.)
And then refresh the pivottable.

You may find that using a dynamic range name makes things easier to add/remove
entries from you data table.

Debra Dalgleish explains dynamic range names here:
http://contextures.com/xlNames01.html#Dynamic

=====
Another suggestion would be to use a single range that holds all the data.
Include a year field and make it a page field in the pivottable.

It may make things much easier if you want to compare/contrast years. You could
drag the year into a row or column header and see differences easier????

 

[Roger Govier]

Hi

Make a copy of the Sheet with your PT on to a new sheet, then click on any
cell within the PT.
On the Pivot Tolls>Options tab>Change Data Source>Point to the source table
for the appropriate Year's data.

 

[ShaneDevenshire]

Hi,

Depending on your source you may be able to
1. Select the pivot table in the new sheet (file)
2. Choose Data, Pivot Table & Pivot Chart Report
3. Click the Back button
4. Select a new source range, (you many need to Browse)

 

[Roger Govier]

He is using XL2007, Shane.
No Wizard with back button.

 

[wayshower]

Hi

Make a copy of the Sheet with your PT on to a new sheet, then click on any
cell within the PT.
On the Pivot Tolls>Options tab>Change Data Source>Point to the source table
for the appropriate Year's data.

Thanks for your response.
I have both 2007-8 and 2008-9 workbooks open. The source data sheet in both
years is 'Financial Figures' and layout is identical in both.
I make a copy of the pivot table to a new sheet , then click on cell in
Pivot table in 2007-8 workbook
When I Options tab>Change Data Source>Point to the source table, I open
2008-9 workbook, sheet 'Financial Figures'
If I just click a cell, only the cell is highlighted. So I selected all the
cells in the workbook as the source data.
However, I then get an error message :
The Pivot Table field name is not valid. To create a pivot table you must
use data that is organised as a list....."
Is there a way round this?

wayshower.

 

[Roger Govier]

Hi

Selecting all cells will give errors.
Every column within a PT has to have a header, hence the error message that
was thrown up.

In each workbook, place cursor in a cell of the relevant table, Choose
Insert tab>Table>My table has Headers.
On the Table tools>Design tab, in the first section Properties, change Table
Name to Data 2007-8 and Data 2008-9 respectively.

When you go through the routine I gave earlier, say you want the source to
be Data 2008-9

 

[ShaneDevenshire]

Hi Roger,

sorry to take so long responding, but I work full time without access to the
newsgroup.

Regarding the Pivot Table Wizard - Of course there is.
1. Put the cursor in the pivot table
2. Press Alt+D, P You are in the wizard, the third step.
3. Click the Back button.

 

[Roger Govier]

Hi Shane

Yes, I am aware that Alt+D+P will invoke the old wizard.
Your instruction to the OP did not say this.

But why would you want to do this in XL2007 to carry out the change of
source, when there is an icon specifically for the purpose in the location I
gave to the OP.

 

[Herbert Seidenberg]

Excel 2007
Consolidate workbooks.
Pivot Table
Here is an example with 2 workbooks.
http://www.savefile.com/files/1863132
http://www.savefile.com/files/1863145
Rather difficult.
Dorothy, there is a Wizard of PT in 2007.

 

[wayshower]

Hi Robert,
Apologies for delayed response.
Thanks for your help - I think I've got it now.
Regards,
wayshower