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?
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

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
Top