VBA to copy a range of data and place it on a new worksheet

daltendavis

New Member
Joined
Jun 26, 2018
Messages
37
I am in need of a VBA to copy range E2:O100 and R2:AJ100 and place them on a new worksheet starting at the third row Column E thanks in advance for any and all help
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

KennyGreens

Board Regular
Joined
Aug 8, 2018
Messages
142
Hi, next time please use the search bar at least a little bit on the website as this is a very simple macro. Either way, please see below, it assumes you want the R2:AJ100 range copied right next to the E2:O100 range. It also assumes that those ranges are on your first sheet, you can change that as necessary.

Code:
Dim newSh As Worksheet


Worksheets.Add after:=Sheets(Sheets.Count)
Set newSh = Sheets(Sheets.Count)


Sheets(1).Range("E2:O100").Copy
newSh.Range("E3").PasteSpecial xlPasteValues


Sheets(1).Range("R2:AJ100").Copy
newSh.Range("G3").PasteSpecial xlPasteValues
 
Last edited:

daltendavis

New Member
Joined
Jun 26, 2018
Messages
37
This only copies E2:F3 and R2:AJ3 ... I have searched quite a bit on here and can't find anything that has worked yet
 

daltendavis

New Member
Joined
Jun 26, 2018
Messages
37
I fixed the overlap issue, changing my "G3" to "Q3" to get the correct spacing, but I still cannot get the rows after the first 2 to copy over
 

KennyGreens

Board Regular
Joined
Aug 8, 2018
Messages
142

ADVERTISEMENT

This only copies E2:F3 and R2:AJ3 ... I have searched quite a bit on here and can't find anything that has worked yet

My apologies, I forgot to change the paste to range from when I tested it, but I'm not sure why it would only copy down to row 3. I just tested it and it copied all rows 2:100 for me. Use this:

Code:
Dim newSh As Worksheet


Worksheets.Add after:=Sheets(Sheets.Count)
Set newSh = Sheets(Sheets.Count)


Sheets(1).Range("E2:O100").Copy
newSh.Range("E3").PasteSpecial xlPasteValues


Sheets(1).Range("R2:AJ100").Copy
newSh.Range("P3").PasteSpecial xlPasteValues
 

daltendavis

New Member
Joined
Jun 26, 2018
Messages
37
Is there anyway I could get this to paste in the same formatting... for the cells being copied, Column E and Column R are both dates. So when they are pasted they become large numbers rather than dates. Thanks!!
 

KennyGreens

Board Regular
Joined
Aug 8, 2018
Messages
142
Is there anyway I could get this to paste in the same formatting... for the cells being copied, Column E and Column R are both dates. So when they are pasted they become large numbers rather than dates. Thanks!!

Yes, to paste as they were previously, use this:

Code:
Dim newSh As Worksheet


Worksheets.Add after:=Sheets(Sheets.Count)
Set newSh = Sheets(Sheets.Count)


Sheets(1).Range("E2:O100").Copy Destination:=newSh.Range("E3")


Sheets(1).Range("R2:AJ100").Copy Destination:=newSh.Range("P3")
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,150
Members
417,010
Latest member
jnuss03

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