Help with Recursive VBA

**tddavid89** · 11-08-2012, 09:23 PM

I have included below a macro that I am running and want to continue to run, but figure there is a better way to do it. As you can see, all I am doing is copying the cell A1 and pasting its value in column AO starting at row 2 and working my way down, pasting the value in each row. I want to print this about 100 times. Is there a way to do this without typing in every row? (Note: The value of A1 keeps changing due to random numbers, otherwise I would just print Range("AO2:AO102"). Thanks!

Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+p
'
    Range("A1").Select
    Selection.Copy
    Range("AO2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Selection.Copy
    Range("AO3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Selection.Copy
    Range("AO4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("A1").Select
    Selection.Copy
    Range("AO5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("A1").Select
    Selection.Copy
    Range("AO6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("A1").Select
    Selection.Copy
    Range("AO7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("A1").Select
    Selection.Copy
    Range("AO8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("A1").Select
    Selection.Copy
    Range("AO9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Selection.Copy
    Range("AO10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("A1").Select
    Selection.Copy
    Range("AO11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("A1").Select
    Selection.Copy
    Range("AO12").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("A1").Select
    Selection.Copy
    Range("AO13").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("A1").Select
    Selection.Copy
    Range("AO14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

**JBeaucaire** · 11-08-2012, 09:29 PM

Try this:

Sub CopyXTimes()
Dim X As Long, i As Long

X = Application.InputBox("Copy A1 how many times?", "Repeat", 100, Type:=1)
If X = 0 Then Exit Sub

    For i = 1 To X
        Range("AO1").Offset(i).Value = Range("A1").Value
    Next i

End Sub