Pivot Table Refresh - Auto insert row

  • Thread starter Dominic LeVasseur
  • Start date
D

Dominic LeVasseur

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.
 
D

Debra Dalgleish

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.
 
D

Dominic LeVasseur

Thanks Debra.

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

Dominic LeVasseur

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
 
D

Debra Dalgleish

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
 
Top