Copy-paste a range 5 times

[Stevenn]

I have a range A3:E70 where A3:E69 have the same formatting and the text in A70:E70 is bold. I want to copy-paste the entire range a number of times with a for ... next-loop. Actually, it's kind of a template system. I make the template A3:E70 and uses it a number of times. How is it possible?

 

[BiocideJ]

You don't necessarily need a loop at all for this, and since you didn't give any information on the criteria for where the formats go it is virtually impossible to give you something to work with using a loop. The following example allows you to specify the sheet/range where you want to copy formats to and another set of ranges to paste that format into.

Public Sub CopyFormats()
    Sheets("Sheet1").Range("A3:E70").Copy                               'source range for formatting
    Sheets("Sheet1").Range("G3").PasteSpecial Paste:=xlPasteFormats     'top-left corner of first destination range
    Sheets("Sheet1").Range("M3").PasteSpecial Paste:=xlPasteFormats     'top-left corner of second destination range
    Sheets("Sheet2").Range("A3").PasteSpecial Paste:=xlPasteFormats     'top-left corner of third destination range
    Sheets("Sheet2").Range("G3").PasteSpecial Paste:=xlPasteFormats     'top-left corner of fourth destination range
    Sheets("Sheet2").Range("M3").PasteSpecial Paste:=xlPasteFormats     'top-left corner of fifth destination range
    Application.CutCopyMode = False
End Sub

 

[JLGWhiz]

I have a range A3:E70 where A3:E69 have the same formatting and the text in A70:E70 is bold. I want to copy-paste the entire range a number of times with a for ... next-loop. Actually, it's kind of a template system. I make the template A3:E70 and uses it a number of times. How is it possible?

Since you did not specify whether you wanted to copy horizontally or vertically, the two macros below provide an option. The procedure will display an input box asking how many times you want to copy. The user must enter an iteger into the box and click OK. Depending on which procedure you use the range will be copied that number of times either horizontally or vertically with a one row or one column space between.

Code:
Sub cpyMultV()
Dim sh As Worksheet, lr As Long
Set sh = Sheets(1)
nbr = Application.InputBox("Enter the number of times to copy.", "TIMES TO COPY", Type:=1)
counter = 0
Do While counter < nbr
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
sh.Range("A3:E70").Copy sh.Range("A" & lr + 2)
counter = counter + 1
Loop
End Sub

Sub cpyMultH()
Dim sh As Worksheet, lr As Long
Set sh = Sheets(1)
nbr = Application.InputBox("Enter the number of times to copy.", "TIMES TO COPY", Type:=1)
counter = 0
Do While counter < nbr
lc = sh.Cells(3, Columns.Count).End(xlToLeft).Column
sh.Range("A3:E70").Copy sh.Cells(3, lc + 2)
counter = counter + 1
Loop
End Sub
Code: