How to Include Multiple Pivot Tables in a Single Sheet/Tab

[McMasher]

Hey Friends,

I recently started digging around to try and find out what the best-practice would be for having multiple pivot tables on one worksheet (and having pivot tables below other pivot tables).

My search Produced the following results:

• "copy the pivot table and paste as values"
• "you should only have one pivot table per worksheet"
• "That simply can't be done"

That being said I wanted to offer up the solution I use:

• Copy and paste as a Linked Picture.

But McMasher it's a picture so it's just a static image right?

• Wrong! If you copy and paste a pivot table as a linked picture and then refresh that pivot table, the linked picture will also update.

But McMasher what if the size (read range) of the pivot table changes?

• Ugh you got me! That's an issue I don't have a direct answer for but some simple VBA could probably target that linked picture object and tell it to use the range of the full pivot table each time the pivot table is updated.

McMasher you PoS I tried using linked pictures and now my workbook is all sorts of broken! screw you!

• Linked pictures tend to be kind of awkward and will make validation lists operate slowly.
• The way that I've gotten around that issue is that I put all of my linked pictures in a separate workbook. for example:
  • Data entry in one workbook. this workbook also has all of the pivot tables you want but within this worksheet they all have their own private little condo (read tab or sheet).
  • copy each pivot table and paste them as a linked picture in one tab/sheet of a separate workbook. I'd name it "Printable Report.xlsx" or something along those lines.
  • Keep the "Printable Report.xlsx" file closed UNTIL you want to update it, then:
    • open it
    • refresh it
    • print it
    • save it
    • close it
  • then continue using your data entry workbook, save and close or do whatever.

Hope this helps someone! Good luck!
~McMasher

 

[Jerry Sullivan]

Hi McMasher,

That's a creative approach to a common obstacle.

I think the only scenario where that would be useful would be for a fixed-size dashboard which required the PivotTables to remain the same size (through scaling) even if the number of rows increased. That would only work if the range of scaling was limited enough to be readable.

For scenarios in which there's a desire to have PivotTables directly below other PivotTables, but the size of the report isn't fixed, then I think there are cleaner approaches using that VBA that don't require pictures.

If the user doesn't need to interact directly with the PivotTables in the report....
Try keeping your "source" Pivots on other sheet(s). When the Pivot's are refreshed and resized then clear the report page and copy-paste the "source" PivotTables to the report sheet accounting for the new size of each Pivot.

If the user needs to interact directly with the PivotTables in the report....
Oversize the number of blank rows between the PivotTables, then use VBA to hide all but 1 or 2 rows between the Pivots each time the PivotTables are updated.

Here's some code that you could use as a starting point to hide the rows between the Pivots.
https://www.mrexcel.com/forum/excel...o-i-get-last-row-pivot-table.html#post3409896

 

[McMasher]

Thanks for the extended options Jerry,

The last missing piece to the puzzle here is column width. The pivot tables that I have created vary in number of columns and column headers. One report has year as the column header, another will have year as the row labels, another will have state as the column headers and widgets as the row labels (widgets being strings that can exceed 250 characters).

Column widths are fixed for a worksheet, but the needed width of any column within a pivot table is fluid, so having two pivot tables below each other, each with varying needs regarding column width, causes the pivot tables to either look really ugly with a lot of white space, or cut off and not showing all of the necessary information.