What's new

VBA coders in here?

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.
 
Sed ego esset diu percussit haesit.

Excel operari elit. Satis ad me , et prorsus VBA domi egestas rutrum . *** sciam an efficens amet ipso facto ordines addere reficite de mensa centralis fama adipiscing elit. Auto et scribe super ordines et columnae , *** relationes rutrum dignissim a cardine ad mensam facit evolvere , sed non est aliquid , quia volo rumpere sapien , maxime quia sunt maxime per has ipsas artes et debilis sum in magistratu Obmutui , quantum potest conatur ita explorata securus mecum laboret. Click feci tortor felis multum una in essentia, per prius removeatur adaequationis tons subitis opus , sed propter aliquam causam istum mihi haesit . Conatus sum, quod fit per hoc , quod accepi a foro ad mensam post refoveo cardine adde ordines nolumus refoveo ordinibus post deinde descendit ad finem . Lorem ipsum dolor sit amet , ut :

Sub test1 ( )
Quod pt Dim PivotTable
Set ActiveSheet.PivotTables v = ( " PivotTable3 " )
Debug.Print pt.TableRange2.Rows ( pt.TableRange2.Rows.Count ) . Aliquam congue
Sub End

Sed rutrum plus quam vellem , et involvit magis gradus ( mensae extremum invenire addito ordines, finem invenire schemate turpis ordines sequentem tabulam referam , etc.)

Quis ideam habere , quam me ad hoc , ut VBA artes ?

TIA .


You're way overestimating us, bro. Best of luck with that work stuff tho.
 
You're way overestimating us, bro. Best of luck with that work stuff tho.

That translation wasn't correct, but I won't get into that now.

Also, you may be right, but it also tells you I am at the end of my options. Next step is to hit up my friend at amazon (sr. programmer) and have him code it for me, or I just go with the cumbersome crappy code that doesn't really get where I need to go.
 
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've done my VBA in Access, not Excel, but I have one idea. If all the tables have the same number of rows when you start and finish, you should be able to put them in separate sheets, select the sheets as a group, and make identical changes to all the sheets at the same time.
 
Sub test1()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("PivotTable3")
Debug.Print pt.TableRange2.Rows(pt.TableRange2.Rows.Count).Row
End Sub

Dude, it's like you're speaking another language entirely. I don't understand a freaking word of that.
 
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.


Did you try turning if off and then turning it back on again?
 
I've done my VBA in Access, not Excel, but I have one idea. If all the tables have the same number of rows when you start and finish, you should be able to put them in separate sheets, select the sheets as a group, and make identical changes to all the sheets at the same time.

That is exactly the problem, we are using pivot tables because the variability in the data makes it the most efficient way to present the data, but that also generates variable length tables. Sometimes there might be 3 rows, sometimes 15. The max would probably be 20, and the min 2.
 
Next step is to hit up my friend at amazon (sr. programmer) and have him code it for me, or I just go with the cumbersome crappy code that doesn't really get where I need to go.

Step 1) ask a bunch of tards on jazzfanz how to code some ****.

Step 2) ask professional coder friend guy how to code some ****.


Welp.
 
Pretty sure there is a VBA forum out there somewhere, where you discuss this kind of stuff all the time (minus the politics of JazzFanz of course).


Best of luck.
 
Pretty sure there is a VBA forum out there somewhere, where you discuss this kind of stuff all the time (minus the politics of JazzFanz of course).


Best of luck.

Yeah I posted it in a few but it isn't interesting enough to get anyone who can really write the code to respond, but it is complicated enough that people like me who can code but can't do everything provided ok responses. I know there are some smart folks in here that I thought for sure used this type of thing (I knew OB did, and maybe colton). So I thought it was worth a shot.
 
Yeah I posted it in a few but it isn't interesting enough to get anyone who can really write the code to respond, but it is complicated enough that people like me who can code but can't do everything provided ok responses. I know there are some smart folks in here that I thought for sure used this type of thing (I knew OB did, and maybe colton). So I thought it was worth a shot.

Yeah I remember I had to get this programmer to help me running some VBA stuff on excel for my dissertation... he charged like $25 an hour if I remember correctly... It was worth it just to get the research done though.
 
Back
Top