I have a macro that add news rows based on a input userbox and I'd like to use the autofill to keep the formulas and also same layout if possible.
Example: I want to add 4 extra products in sheet1. Now, the macro (my button) creates 4 new rows below row #24 (Product1), row #28, and row #32. I want to apply the original formulas in columns O, AB, AO, BB and BO to the new inserted rows. How can I adapt the following code?
- 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
Besides, I want to do the exact same thing on sheet2, but in which values of cells are the same as sheet1 (as already set)
Thanks for your help!