Run Macro number of times equal to integer value in a specific cell

I would like a macro to execute a number of times equal to the integer value in a specific cell

When Cell "A1" is populated with a positive whole number integer, I want the following macro to run a number of times equal to that integer.

Sub CopyRow()
  Rows("2:2").Select
  Selection.Copy
  Rows("3:3").Select
  Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub

2 answers

  • answered 2019-08-13 04:33 Mikku

    Try pasting this in the Worksheet

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.Address(0, 0) = "A1" Then
    
    Dim i As Long
    
         Rows("2:2").Copy
    
         For i = 1 To Range("A1").Value
            Rows("3:3").Insert Shift:=xlDown
         Next
    
    End If
    
    End Sub
    

    Also I strongly suggest to Avoid Using Select in VBA.

    Paste the code as in the Screenshot.

    enter image description here

  • answered 2019-08-13 04:48 Salim Hasbaya

    Try This Macro

    Sub Add_rows()
    Rows(2).Copy
      Rows(2 & ":" & [A1] + 1).Insert
    End Sub