Insert a blank row below a Pivot Table VBA

[Jeevz_87]

Hi All,

I have a workbook which has two pivot tables stacked on top of each other which are likely to grow in the coming months as the source data is updated.
What I'm looking for is a macro that can find the bottom of these pivot tables and insert two blank row(s) each to mitigate any overlap errors as the pivot tables increase in size.
Is there a simple piece VBA code to do this?

Thanks,

Jvz_87

 

[BSALV]

i'd prefer a solution with 100 hidden rows between both and unhide those rows when the 1st pivottable "grows"

 

[Jeevz_87]

That's exactly what I have at the moment and normally I have no issue with it but I was wondering if there is a more elegant solution to this issue?

 

[BSALV]

or something like this

Sub Macro1()
     Set c1 = ActiveSheet.PivotTables(1).TableRange2            '1st pvt
     Set c2 = ActiveSheet.PivotTables(2).TableRange2            '2nd pvt

     MsgBox c1.Address & vbLf & c2.Address

     ActiveSheet.PivotTables(2).Location = "'" & ActiveSheet.Name & "'!" & c1.Cells(c1.Rows.Count + 5, 1).Address     '2nd pivottable 5 rows below 1st

End Sub

adjust that 5 for the number of rows your pivottable is max growing everytime.