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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!!
 
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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