Adjusting Rows with variable pivot table height

[treinhardt]

Hello Everyone!


Here is my situation... I have a "invoice" per say in a excel sheet that consists of 3 pivot tables stacked one above each other. I have to have them one after the other and not side by side due to it has to fit on one print sheet and look "professional".


MY PROBLEM is, some customers have 1-2 rows of data in each pivot tables thus leaving TONS of blank rows between the three tables. THen some customers have 15-20 line items per pivot table. So A: i need to find a way to "automatically" adjust the space between pivot tables to ensure that when there is tons of data, it doesnt overrun the pivot table below it and B: when there is little data, it shrinks the gaps between the two to look better.


Is something like this possible or does anyone else have a better idea on how to fit all this data "dynamically" on one sheet?

 

[Luke M]

Are macros an acceptable option?

If this is just for a print-out, another possible option is to use camera links to show the PT's right next to each other, when in reality they are side by side (or on different worksheets).

 

[treinhardt]

Yes Macro's are fine and Yes this is literally just for a print off to send to the customer.

 

[jeffreyweir]

Give the range that your pivot tables are within the name 'PivotTables' then run this code:

[pre]

Sub HideSpaceBetweenPivots()
Dim pt As PivotTable

Dim rngColumns As Range
Set rngColumns = Range("PivotTables").EntireRow

rngColumns.Hidden = True

For Each pt In ActiveSheet.PivotTables

If Not Intersect(pt.TableRange2, rngColumns) Is Nothing Then
pt.TableRange2.EntireRow.Resize(pt.TableRange2.Rows.Count + 1).Hidden = False
End If
Next
End Sub

[/pre]
If you want more space between the pivots, change the + 1 to + x where x is the amount of rows you want between pivots.

 

[jeffreyweir]

You can also have that code automatically triggered whenever the pivot changes (e.g. in response to someone filtering or unfiltering a pivot field, by putting the below code in the SHEET module where the pivots are:

[pre]

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim pt As PivotTable

Dim rngColumns As Range
Set rngColumns = Range("PivotTables").EntireRow

rngColumns.Hidden = True

For Each pt In ActiveSheet.PivotTables

If Not Intersect(pt.TableRange2, rngColumns) Is Nothing Then
pt.TableRange2.EntireRow.Resize(pt.TableRange2.Rows.Count + 1).Hidden = False
End If
Next
End Sub

[/pre]