Copy Paste range multiple times

vfa

New Member
Joined
Mar 18, 2009
Messages
4
Hi, I hope someone can help me with developing a macro. Please note that I have no VBA knowledge but have experimented with recording very basic macros in the past...

Here's the challenge:

I would like to copy and paste a range multiple times from worksheet1 to worksheet2. More specifically, the range will always consist of two adjacent columns with a varying number of rows, let's say B1:C100 for example. So if I need to copy it 6 times, starting the paste on B1 of worksheet2, the result would be B1:C600 would have the range in it (6 times, one right on top of the other).

I hope this makes sense and wasn't too bad of an explanation. Thanks in advance for your assistance.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Hi vfa,

Welcome to the board!


This will copy the selected range 6 times to Sheet2:
Code:
Sub Copy6Times()
rng = Selection.Address
    For t = 1 To 6
        LR = Sheets("Sheet2").Range("B65536").End(xlUp).Row + 1
        Range(rng).Copy Sheets("Sheet2").Range("A" & LR)
    Next t
End Sub
 

vfa

New Member
Joined
Mar 18, 2009
Messages
4
Thank you Datsmart! You've saved me a LOT of time tonight. Cheers!
 

achonikolov

New Member
Joined
May 28, 2012
Messages
12
Guys,

I have almost the same issue.
1. I need to copy range from sheet1 a1 to empty cell as many times as we have fill coloumn and paste to sheet2 starting from a2.
2. Then I need to copy range c4 to end from sheet1 and then paste to sheet2 starting from c2
3. Copy range d4 to end from sheet1 and paste to sheet2 starting from d2
4. Copy range e4 to end from sheet1and paste to sheet2 starting from e2

I have a file for example but dont know how to attach to post

I will be very grateful if you can help me.
if the place of this post is not here please move it but I decided to put a topic here.
Thanks in advance.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,119
Messages
5,599,821
Members
414,341
Latest member
Mohammedsobhey

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