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

[daltendavis]

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

 

[KennyGreens]

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.

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

 

[daltendavis]

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]

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]

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:

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]

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]

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:

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")