Excel 2007 : Multiple Pivot Tables on one sheet

**AltaS** · 03-09-2011, 08:04 AM

My aim is to insert multiple pivot tables on one sheet (BELOW) each other for project expense reporting.
All works fine except for the positioning of the second pivot table insertion point as the top pivot table has different number of output rows per project.
Would a macro running each pivot table be the answer- then to establish the last row of data, insert a blank row before running the next pivot table
Your suggestions are appreciated. I am not very well trained in macro's and would like some inputs please.
Thank you

**MarvinP** · 03-09-2011, 10:30 AM

Hi AltaS and welcome to the forum,

In my experience with data below a pivot table. When the Pivot Table refreshes it may be longer (more rows) so it gives a warning that the rows below what it needs will be overwritten. This happens even if a single number is put in a cell below the pivot table.

Because of this problem, I make multiple pivot tables on a single sheet next to each other instead of below.

I hope this helps.

**DonkeyOte** · 03-09-2011, 10:41 AM

Originally Posted by MarvinP

I make multiple pivot tables on a single sheet next to each other instead of below.

Though of course the above practice also assumes a static column range of sorts

(granted the permissible number of columns is more restricted than rows)

IMO if you can't be sure of resizing it's safest to store Pivots on separate sheets.

If you then want a single sheet for print out / display purposes (ie no functional drill through requirements) you can use VBA to take copies of the TableRange2 range and paste as values in sequential manner on your summary sheet (rather than duplicating the tables)

**MarvinP** · 03-09-2011, 10:46 AM

There was a comment in a Mr Excel book where copy and paste values only, was his prefered method to get pivot answers on a summary/dashboard sheet. I thought that strange until I kept getting error messages from my pivot refresh commands.

I'm searching the net now to see how Excel determines how much room it needs to refresh a pivot. I guess the answer to this question is "be careful where you put multiple pivots on a single sheet"?

**AltaS** · 03-10-2011, 08:38 AM

Dear MarvinP
I realized putting pivots under each other is a problem.
But (the big but) my boss wants the reporting like that.
I successfully created the report with pivots next to each other. He did not like it at all.
I really appreciate all responses and will try all.
I am going thru the reading recommended by DonkeyOte

**DonkeyOte** · 03-10-2011, 08:41 AM

Originally Posted by AltaS

I am going thru the reading recommended by DonkeyOte

The recommended reading links are just general (signature) they do not relate specifically to your question.

As far as I can tell you didn't answer the question as to whether or not you need the actual Pivot functionality (filtering, drill through etc) on your summary sheet or just the resulting values ?

**AltaS** · 04-12-2011, 06:13 AM

Back to report. DonkeyOte yes I do need the actual Pivot functionality (filtering, drill through) on summary sheet. Your response once again is appreciated. Alta

**Mike Dee** · 03-27-2012, 07:20 PM

Bit late in this reply - but doing so in case of new visitors.

In Excel 2010 - there is an option to Move Pivot Tables - in the Ribbon > Pivot Table > Options > Move (appears when you click inside the PT)

You could firstly create the PTs side by side, Copy the Worksheet (for the boss) and Move the PTs within the copied worksheet.

The only drawback is when you refresh/update the PTs, you might have to delete and then recreate the Copied Worksheet everytime so as to prevent the PTs from overwriting PTs below.

Hope this helps

Mike