Inserting multiple rows based off value in a cell

[MrBalls1983]

Hello
I'm trying to create a Form button that will insert an exact copy of a single row (formales, values and formating). The number of rows to insert is to be determined by a value in a cell. I've screenshot my worksheet below.

Basically cell L4 contains how many rows i want to add. Row A9:W9 is the row i would like to base the row insert off.

any help would be much appreciated.

 

[MikeVol]

Hello @MrBalls1983.
Maybe

Option Explicit

Sub AddRowsBasedOnTemplate()
    Dim i           As Long

    Dim ws          As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")    ' Replace Sheet1 with the name of your worksheet

    Dim templateRow As Range
    Set templateRow = ws.Range("A9:W9")

    Dim insertCount As Long
    insertCount = ws.Range("L4").Value

    If Not IsNumeric(insertCount) Or insertCount <= 0 Then
        MsgBox "Please specify the correct number of rows in the cell L4.", vbExclamation
        Exit Sub
    End If

    For i = 1 To insertCount
        templateRow.Offset(1).EntireRow.Insert
        templateRow.Copy templateRow.Offset(1)
    Next i

    ' Clear the contents of the pasted rows if needed, but leave the formatting
    templateRow.Offset(1).Resize(insertCount, templateRow.Columns.Count).ClearContents
    MsgBox "Added " & insertCount & " row(s).", vbInformation

    ' Freeing up memory
    Set templateRow = Nothing
    Set ws = Nothing
End Sub

But I'm not sure I understood you correctly. I hope so and I helped you. Good luck.

 

[MrBalls1983]

Hello @MrBalls1983.
Maybe

Option Explicit

Sub AddRowsBasedOnTemplate()
    Dim i           As Long

    Dim ws          As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")    ' Replace Sheet1 with the name of your worksheet

    Dim templateRow As Range
    Set templateRow = ws.Range("A9:W9")

    Dim insertCount As Long
    insertCount = ws.Range("L4").Value

    If Not IsNumeric(insertCount) Or insertCount <= 0 Then
        MsgBox "Please specify the correct number of rows in the cell L4.", vbExclamation
        Exit Sub
    End If

    For i = 1 To insertCount
        templateRow.Offset(1).EntireRow.Insert
        templateRow.Copy templateRow.Offset(1)
    Next i

    ' Clear the contents of the pasted rows if needed, but leave the formatting
    templateRow.Offset(1).Resize(insertCount, templateRow.Columns.Count).ClearContents
    MsgBox "Added " & insertCount & " row(s).", vbInformation

    ' Freeing up memory
    Set templateRow = Nothing
    Set ws = Nothing
End Sub

But I'm not sure I understood you correctly. I hope so and I helped you. Good luck.

Thankyou for your help. Its coming up with an error when I run it.

 

[MikeVol]

Hi. There are no errors on my side. Perhaps in your original file the 9th row is not (A9:W9).