Pivot table overwrites neighbouring cells

  • Thread starter Thread starter Anissa
  • Start date Start date
A

Anissa

Hello,

I have multiple pivot tables on the same worksheet to allow for easy
comparison of data. The source data is based on the week and expected to be
refreshed daily. As the week progresses the size of the pivot tables will
grow and overwrite data below it.

I am looking for a workaround to this problem or if there is a way to set
the pivot table to insert a new row below it if the size will increase and
before data is updated.

Thanks in advance,

Anissa
 
Hi,

There is no feature of pivot tables to do what you wish. You pretty much
have two options that I can think of:
1. Prepare extra space before you refresh your pivot tables. For example
suppose your first PT was A1:D10 then put in 10 or 20 blank lines to the next
pivot table. If you want to get fancy you could set up a filter column which
allows you to hide extra blank rows quickly so that all the pivot tables look
like they are adjacent.
2. You could write a VBA routine which inserted extra rows before
refreshing and removed any extras afterwords. You could attach the code to
the

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

End Sub

procedure.
 
Back
Top