LogGrad98
Well-Known Member
Contributor
20-21 Award Winner
2022 Award Winner
2023 Award Winner
2024 Award Winner
Might be a long shot but I am stuck.
I work in Excel a lot. I am pretty good at it, and code lots of my stuff directly in VBA. I am struggling with an efficient code to add rows automatically upon refresh of a pivot table for report building. The auto over-write of rows and columns when refreshing a pivot table makes developing reports cumbersome, but it isn't anything I want to break out Access for, mainly because most people using these reports are very very weak on office skills and I am trying to dumb the reports down as much as possible so they are very easy to work with. I have made much of it a single click of a macro button, in essence, eliminating tons of work they did before in updating this stuff, but I am stuck on this one for some reason. I have tried something to the effect of this, which I got from another forum, to find the end of the pivot table before refresh, then add rows, then find the end again after refresh and shrink the rows back down. Here is that code:
Sub test1()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("PivotTable3")
Debug.Print pt.TableRange2.Rows(pt.TableRange2.Rows.Count).Row
End Sub
But it is still more cumbersome than I would like, with way more steps involved (find the end of the table, add rows, find the end of the new table, delete rows, repeat for next table, etc.).
Anyone with VBA skills that has an idea how I could approach this?
TIA.
I work in Excel a lot. I am pretty good at it, and code lots of my stuff directly in VBA. I am struggling with an efficient code to add rows automatically upon refresh of a pivot table for report building. The auto over-write of rows and columns when refreshing a pivot table makes developing reports cumbersome, but it isn't anything I want to break out Access for, mainly because most people using these reports are very very weak on office skills and I am trying to dumb the reports down as much as possible so they are very easy to work with. I have made much of it a single click of a macro button, in essence, eliminating tons of work they did before in updating this stuff, but I am stuck on this one for some reason. I have tried something to the effect of this, which I got from another forum, to find the end of the pivot table before refresh, then add rows, then find the end again after refresh and shrink the rows back down. Here is that code:
Sub test1()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("PivotTable3")
Debug.Print pt.TableRange2.Rows(pt.TableRange2.Rows.Count).Row
End Sub
But it is still more cumbersome than I would like, with way more steps involved (find the end of the table, add rows, find the end of the new table, delete rows, repeat for next table, etc.).
Anyone with VBA skills that has an idea how I could approach this?
TIA.