Paste a copied selection a variable number of times based on a counter

[weasel21]

I need to create a sheet whereby a copied section repeats a variable number of times.

The below is what i have come up with. It is written to find the first empty row in the sheet paste in the copied selection. Then repeat as many times as per the number in cell AH2.

Right now my macro is only pasting once regardless of what I try.

Sub Z_CreateRepeatedSections()
    
Dim i As Long
Dim LastRowColumnX As Long

LastRowColumnX = Cells(Rows.Count, 1).End(xlUp).Row

Range("A17:T28").Copy
   For i = 1 To Range("AH2").Value
   Range("A" & LastRowColumnX + 1).Select
   ActiveSheet.Paste
   Next i


End Sub

 

[Osvaldo Palmeiro]

Try:

Sub Z_CreateRepeatedSections()
 Dim i As Long
 Dim LastRowColumnX As Long
  Range("A17:T28").Copy
   For i = 1 To Range("AH2").Value
    LastRowColumnX = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A" & LastRowColumnX + 1).PasteSpecial Paste:=xlPasteAll
   Next i
End Sub

 

[mcwillis81]

Hi Weasel21

You don't obtain your last row number within your for statement which means no matter how many times you paste you're always pasting in the same range e.i. "a29"

Try this

Sub Z_CreateRepeatedSections()
    
Dim i As Long
Dim LastRowColumnX As Long

'don't put it here
'LastRowColumnX = Cells(Rows.Count, 1).End(xlUp).Row


Range("A17:T28").Copy


   For i = 1 To Range("AH2").Value

'put it here
   LastRowColumnX = Cells(Rows.Count, 1).End(xlUp).Row


    Range("A" & LastRowColumnX + 1).Select
    ActiveSheet.PasteSpecial

   Next i




End Sub

 

[jolivanes]

Avoid, if possible, looping and/or selecting.

Sub MultiCopy() 
    Dim myRange As Range 
    Dim numCopies As Long 
    Set myRange = Range("A17:T28") 
    numCopies = Range("AH2").Value
    If numCopies <= 0 Then Exit Sub 
    myRange.Copy myRange.Resize(myRange.Rows.Count * (1 + numCopies), 1) 
End Sub

 

[weasel21]

Thanks for this! Sorted now