Hey all,

Haven't been able to track this one down.

I have a report page with three pivot tables, one below the other. Is there
anyway to maintain their spacial relationship when they get updated? That is,
if new information is added in the source data, and pivottable 1 gets larger
(more rows) that data dumps over whatever is in those rows below. I would
like the refresh action to insert rows when necessary so that pivots 2 and 3
move down accordingly. Same for when data is removed from source data (less
important).

Any ideas?

Thanks.

There's no built-in way to accomplish this. You could programmatically
move the pivot tables that are below the one that's being refreshed,
refresh the upper pivot table, then move the lower pivot tables back,
below the new last row.

Dominic LeVasseur wrote:
I have a report page with three pivot tables, one below the other. Is there
anyway to maintain their spacial relationship when they get updated? That is,
if new information is added in the source data, and pivottable 1 gets larger
(more rows) that data dumps over whatever is in those rows below. I would
like the refresh action to insert rows when necessary so that pivots 2 and 3
move down accordingly. Same for when data is removed from source data (less
important).

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks Debra.

Do you know the VBA property for a pivot tables last used row?



"Debra Dalgleish" wrote:

There's no built-in way to accomplish this. You could programmatically
move the pivot tables that are below the one that's being refreshed,
refresh the upper pivot table, then move the lower pivot tables back,
below the new last row.

Dominic LeVasseur wrote:
I have a report page with three pivot tables, one below the other. Is there
anyway to maintain their spacial relationship when they get updated? That is,
if new information is added in the source data, and pivottable 1 gets larger
(more rows) that data dumps over whatever is in those rows below. I would
like the refresh action to insert rows when necessary so that pivots 2 and 3
move down accordingly. Same for when data is removed from source data (less
important).

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

For those interested, this is the code I came up with:

Private Sub Worksheet_Activate()
Dim pt2lastrow, pt1lastrow As Integer
Application.ScreenUpdating = False

ActiveSheet.PivotTables("PivotTable1").TableRange2 .Cut
ActiveSheet.Range("AA1").Activate
ActiveSheet.Paste

ActiveSheet.PivotTables("PivotTable3").TableRange2 .Cut
ActiveSheet.Range("BA1").Activate
ActiveSheet.Paste

ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh
pt2lastrow = Cells(Rows.Count, "A").End(xlUp).Row

ActiveSheet.PivotTables("PivotTable1").TableRange2 .Cut
ActiveSheet.Cells(pt2lastrow + 2, "A").Activate
ActiveSheet.Paste

ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
pt1lastrow = Cells(Rows.Count, "A").End(xlUp).Row

ActiveSheet.PivotTables("PivotTable3").TableRange2 .Cut
ActiveSheet.Cells(pt1lastrow + 2, "A").Activate
ActiveSheet.Paste

ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh

Columns("A:C").EntireColumn.AutoFit
Range("A1").Activate

Application.ScreenUpdating = True
End Sub


"Dominic LeVasseur" wrote:

Thanks Debra.

Do you know the VBA property for a pivot tables last used row?



"Debra Dalgleish" wrote:

There's no built-in way to accomplish this. You could programmatically
move the pivot tables that are below the one that's being refreshed,
refresh the upper pivot table, then move the lower pivot tables back,
below the new last row.

Dominic LeVasseur wrote:
I have a report page with three pivot tables, one below the other. Is there
anyway to maintain their spacial relationship when they get updated? That is,
if new information is added in the source data, and pivottable 1 gets larger
(more rows) that data dumps over whatever is in those rows below. I would
like the refresh action to insert rows when necessary so that pivots 2 and 3
move down accordingly. Same for when data is removed from source data (less
important).

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks for posting your solution. Another way to determine the last row
of a pivot table:

Sub test1()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("PivotTable3")
Debug.Print pt.TableRange2.Rows(pt.TableRange2.Rows.Count).Row
End Sub


Dominic LeVasseur wrote:
For those interested, this is the code I came up with:

Private Sub Worksheet_Activate()
Dim pt2lastrow, pt1lastrow As Integer
Application.ScreenUpdating = False

ActiveSheet.PivotTables("PivotTable1").TableRange2 .Cut
ActiveSheet.Range("AA1").Activate
ActiveSheet.Paste

ActiveSheet.PivotTables("PivotTable3").TableRange2 .Cut
ActiveSheet.Range("BA1").Activate
ActiveSheet.Paste

ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh
pt2lastrow = Cells(Rows.Count, "A").End(xlUp).Row

ActiveSheet.PivotTables("PivotTable1").TableRange2 .Cut
ActiveSheet.Cells(pt2lastrow + 2, "A").Activate
ActiveSheet.Paste

ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
pt1lastrow = Cells(Rows.Count, "A").End(xlUp).Row

ActiveSheet.PivotTables("PivotTable3").TableRange2 .Cut
ActiveSheet.Cells(pt1lastrow + 2, "A").Activate
ActiveSheet.Paste

ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh

Columns("A:C").EntireColumn.AutoFit
Range("A1").Activate

Application.ScreenUpdating = True
End Sub


"Dominic LeVasseur" wrote:


Thanks Debra.

Do you know the VBA property for a pivot tables last used row?



"Debra Dalgleish" wrote:


There's no built-in way to accomplish this. You could programmatically
move the pivot tables that are below the one that's being refreshed,
refresh the upper pivot table, then move the lower pivot tables back,
below the new last row.

Dominic LeVasseur wrote:

I have a report page with three pivot tables, one below the other. Is there
anyway to maintain their spacial relationship when they get updated? That is,
if new information is added in the source data, and pivottable 1 gets larger
(more rows) that data dumps over whatever is in those rows below. I would
like the refresh action to insert rows when necessary so that pivots 2 and 3
move down accordingly. Same for when data is removed from source data (less
important).

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html