Hello everyone! I'm back on the forum since I need to dig into some VBA code (or not) that could help me on the following issue:
I want to create a textbox message where the user needs to give an input (= number of different products or services), then, macro will create/remove rows according this input on sheet1 and sheet2, for 3 different sections (volumes, unit prices & turnover).
Note that in sheet 1: column O is a formula, last year goes to 2025 (column BO). While sheet2: is refering directly to sheet1 (simply = the cell), same structure
The idea is then to avoid to complete manually the information when adding or removing products / service lines.
I hope it's clear, but don't hesitate to ask me for more info..
How can I start with the code?
Thanks a lot!
VBA user input and adapt rows (in 2 sheets)
- jeoffrey3
- Thread is marked as Resolved.
-
You need to attach an example file.
-
Where do you want to add or remove rows?
-
Hi, thanks for your return.
Assuming I want to add 4 extra products or services (column A) between row #24 and row #29, I want those same new rows to be reflected between rows #33 and #38, #42 and #47, #57 and #62 and finally #66 and #71.
Other option could be to add just after the initial products or services so as from row #29 or juste before row #24, but this wouldn't solve the issue of removing rows manually if I have only 2 products or services.
Since this file will be used for different target groups, it's difficult to anticipate the number of rows per section...
Hope this is clearer!
-
-
The best way would probably be to get the user to select the cell where roes are required thn add the rows below.
-
Hi jeoffrey,
you can try an approach like this, the code is already on the sheet in a module- note the markers on the sheet and the button:
Code
Display MoreOption Explicit Sub NewPoS() Application.ScreenUpdating = False Dim MyN As String Dim i As Long, MyMarker As Long, MyM As Long, LstRW As Long Dim ws As Worksheet: Set ws = Feuil1 MyN = InputBox("Give me a number", "My Input Box") If Not IsNumeric(MyN) Then Exit Sub MyN = CInt(MyN) For MyMarker = 1 To 5 LstRW = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row MyM = Application.Match("Marker" & MyMarker, ws.Range(ws.Cells(1, 1), ws.Cells(LstRW, 1)), 0) For i = 1 To MyN ws.Rows(MyM + 2).EntireRow.Insert shift:=xlUp Next i Next MyMarker End Sub -
Hi Justin,
Thanks a lot for this!
I'll use that as a basis, putting at first 1 row then, if needed, the user will add the extra rows via the macro.May I ask you to indicate me in the code the following:
- To keep the formulas (SUM) in the columns O, AB, AO and such (white cells) and
- To keep the same layout as well (lines around the cells, etc..?
And finally, how can I get the code to do the exact same thing on the sheet2 (setting up the same rows and layout as above) but where values in cells are just equal to (=) cells from sheet1?
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!