Multiple Pivot Tables - Custom Grand Total, Hide Blank Rows, Prevent Overlapping Tables

[Yodelayheewho]

I hope I've entered this post in the correct location on the forum.
Attached is an image that explains what's going on and the help I need. I'm NOT very experienced with pivot tables.
I have four pivot tables, one above the other, on a worksheet.

1. With refresh and/or changing the slicers, I want the tables to expand and contract accordingly without overlapping or leaving huge gaps between each table. I have 'guessed' the maximum rows for each table and added these under the each.
I found this code on another forum (please let me know if I need to provide credit) and it has gotten me this far. However, if you look at the image I attached, you'll see the steps in purple that show where I need some help. The code works, except some blank rows are not hidden as expected and it is inconsistent between tables.

'***Removes blank rows between two pivot tables***
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  Dim rA As Range

  Const RowsToLeave As Long = 2 '<- Edit as needed, represents the number of rows between pivot tables

  Application.ScreenUpdating = False
  Cells.EntireRow.Hidden = False
  For Each rA In Range("a2:a84", Range("a" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Areas
    If rA.Rows.Count > RowsToLeave Then rA.Resize(rA.Rows.Count - RowsToLeave).EntireRow.Hidden = True
  Next rA
  Application.ScreenUpdating = True
End Sub

2. I need specific verbiage for each row that contains totals. I could not figure out a way to customize the Grand Total row to what I need. So, I had to create a row outside of the pivot table and add a formula to get the totals. But now I have duplicate totals. If I hide the Grand Total line, the formula fails.

If you look at the bottom right of the image attached, it shows my ideal layout.

Thank you in advance for your consideration!

 

[GraH - Guido]

Thread should be moved to VBA section.

 

[Yodelayheewho]

I was teetering on VBA or Excel. I appreciate your direction. What is the best method for moving this post to VBA?

 

[Yodelayheewho]

Bump

 

[Yodelayheewho]

I resolved my challenge. I'm new to VBA, so if there is a better way to do this, please let me know. Below I've supplied the code and an image of what the worksheet looks like. GraH, I appreciate your help and that of so many others. I hope this helps someone else.

'***Hide blank rows between multiple pivot tables AND after last pivot table between pivot table and other data***
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  Dim rA As Range
  Const RowsToLeave As Long = 4 '<- Edit as needed, represents the number of rows to leave unhidden between each pivot table
  Application.ScreenUpdating = False 'Eliminates screen updating process while the code runs
  Cells.EntireRow.Hidden = False
  For Each rA In Range("b2", Range("b" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Areas
    If rA.Rows.Count > RowsToLeave Then rA.Resize(rA.Rows.Count - RowsToLeave).EntireRow.Hidden = True
  Next rA
  Dim bL As Range
  Const RowsToShow As Long = 1 '<- Edit as needed, represents the number of rows to leave unhidden (show) between the last pivot table and non-pivot data below
  For Each bL In Range("P103:P127", Range("p" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Areas
    If bL.Rows.Count > RowsToShow Then bL.Resize(bL.Rows.Count - RowsToShow).EntireRow.Hidden = True
  Next bL
  Application.ScreenUpdating = True
End Sub