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!
You need to attach an example file.
Here it is!
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.
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
- Option 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
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?