If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 A110 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 |
Thread Tools | |
Display Modes | |
|
|