Multiple sheet Pivot table

[Chris]

Dear All

Little problem with my pivot table I am trying to build.
I need to consolidate multiples sheets (all identical layout & headers) but
when I do using the multiple consolidatioin range it all breaks down.

The data is layed out like this:

Nominal Code P&L Department Month Budget Total

I need to end up with a pivot table with

Department as a page
Nominal codes in the rows
Month as a column header
Budget total in "Data"

So it look like this

Department (All)

Sum of Budget Total Month
P&L Nominal Codes Jan Feb Grand Total
Sales 4015 / Radio 22000 23000 45000
4016 / Web 2500 2500 5000
Grand Total 24500 25500 50000

Each worksheet will contain the data for different nominal codes (no overlap).

Looking forward to your answers

 

[Roger Govier]

Hi Chris

You will need to get all of your data to one sheet before using the PT to
achieve what you want.
Take a look at Ron de Bruin's site for some code to help you achieve this.
http://www.rondebruin.nl/copy2.htm

Then use the sheet with the amalgamated data to create your PT.

 

[Lori]

Here's a simple method to consolidate all data in up to 24 sheets in order to
create the pivot. It's petty quick with the keyboard shortcuts.

- Group all the sheets (by clicking the last sheet and pressing Shift)
- Select data to the very last row a2:e65536 (using Ctrl+shift+arrow keys)
- Copy to the clipboard select new sheet, etc. (Ctrl+C Ctrl+PgDn repeatedly)
- Now select the new sheet and click the Paste All clipboard button

 

[Chris]

Perfect!

Thank you very much

Hi Chris

You will need to get all of your data to one sheet before using the PT to
achieve what you want.
Take a look at Ron de Bruin's site for some code to help you achieve this.
http://www.rondebruin.nl/copy2.htm

Then use the sheet with the amalgamated data to create your PT.