Pivot Table Dynamic Output Range

[gregoryjgold]

Hi,

I have multiple pivot tables that are summarizing a large table with different filters. They are on top of each other and separated by headings. The problem is that if a new row is added to the table and the pivot table is refreshed it will overwrite on the next available line below the pivot table and eventually overwrite the headings. I am hoping that there is a way to have a more dynamic output range where a new row resulting from the pivot table refreshing will just push the remaining stuff down a row rather than overwriting it. Is this possible? Any advice would be greatly appreciated.

Thanks in advance,
Greg

 

[Comfy]

There maybe a better solution but I have had the same issue and did the following:

Leave a large amount of rows between your pivot tables.

Hide the rows that separate the pivots.

Add code to a pivot table event that unhides and rehides the appropriate rows.

 

[Comfy]

Took me a while to find it but here is the code.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim Row As Long
Row = Target.TableRange1.Row + Target.TableRange1.Rows.Count + 1
ActiveSheet.Rows(Target.TableRange1.Row & ":" & Row - Row Mod 100 + 98).Hidden = False
ActiveSheet.Rows(Row & ":" & Row - Row Mod 100 + 98).Hidden = True
Application.ActiveSheet.UsedRange.Columns.AutoFit
End Sub

I have 3 pivots each starting in row 104, 204 and 304.

This unhides all the rows below the pivot and then rehides as appropriate.

This leaves two blank rows between my pivot tables.

 

[gregoryjgold]

Ok great thank you. I put the code into the sheet under objects and when I hit refresh it seems to be working well. I have a few questions. I noticed that the +1 can be modified to add multiple blank rows below the first pivot table. Would it be possible to modify the space above the next pivot table instead because I have some rows of formatted headings above each pivot table. Also will any other parts of the code need to be modified if I have more and more pivot tables?

 

[Comfy]

The code won't need to be modified if you add more pivot tables you will just have to remember where to place the next one.


* On what rows will your pivot tables be placed (how many rows will be spacers)?
* How many blank rows would you like to be visible after a pivot and before any are hidden?
* How many blank rows would you like to be visible before a pivot and after any are hidden?