Pivot table overwrites neighbouring cells
 

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

Pivot table overwrites neighbouring cells
 

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Anissa" wrote:

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