I'm trying to get VBA to get a table in my worksheet to match the number of rows in a pivot in the same worksheet; I searched online and found the following code:
VBA in module:
Sub ResizeTable()
Dim pt As PivotTable
Dim tbl As ListObject
Set pt = ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1")
Set tbl = ThisWorkbook.Sheets("Pivot").ListObjects("Table1")
tbl.Resize tbl.Range.Resize(pt.TableRange2.Rows.Count, tbl.ListColumns.Count)
End Sub
VBA in worksheet:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim tbl As ListObject
Set tbl = Me.ListObjects("Table1") ' Change "Table1" to your table name
' Resize the table based on the pivot table size
tbl.Resize tbl.Range.Resize(Target.TableRange2.Rows.Count, tbl.ListColumns.Count)
End Sub
It works, except it adds two rows too many to the table, such that with the subtotal it ends up being two rows past the pivot...not the end of the world but I'd like to get it matching and to understand why it wasn't...
VBA in module:
Sub ResizeTable()
Dim pt As PivotTable
Dim tbl As ListObject
Set pt = ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1")
Set tbl = ThisWorkbook.Sheets("Pivot").ListObjects("Table1")
tbl.Resize tbl.Range.Resize(pt.TableRange2.Rows.Count, tbl.ListColumns.Count)
End Sub
VBA in worksheet:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim tbl As ListObject
Set tbl = Me.ListObjects("Table1") ' Change "Table1" to your table name
' Resize the table based on the pivot table size
tbl.Resize tbl.Range.Resize(Target.TableRange2.Rows.Count, tbl.ListColumns.Count)
End Sub
It works, except it adds two rows too many to the table, such that with the subtotal it ends up being two rows past the pivot...not the end of the world but I'd like to get it matching and to understand why it wasn't...