Copy-paste a range 5 times

Stevenn

Active Member
Joined
Feb 8, 2012
Messages
259
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?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.

Code:
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
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top