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

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.

--
Regards
Roger Govier

"Chris" wrote in message
...
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

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" wrote:

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

Perfect!

Thank you very much

"Roger Govier" wrote:

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.

--
Regards
Roger Govier

"Chris" wrote in message
...
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