In need of VBA that copies a range and pastes on new worksheet in the workbook

daltendavis

New Member
Joined
Jun 26, 2018
Messages
37
I am in need of a VBA macro I can apply to a button to copy range E2:O100 and R2:AJ100 on work sheet titled "Historical Orders" to its own new worksheet beginning at cell A2. The new worksheet can have sheet1/2/3/etc. as a name that does not matter.

This process will be repeated every few months so multiple new work sheets will be created if that raises any problems. Thanks in advance for any and all help.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,727
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub daltendavis()
   With Sheets("Historical Orders")
      Sheets.Add , Sheets(Sheets.Count)
      .Range("E2:O100,R2:AJ100").Copy Range("a2")
   End With
End Sub
 

daltendavis

New Member
Joined
Jun 26, 2018
Messages
37
This VBA created a new sheet, however didnt copy the data from sheet-Historical Orders-
 

daltendavis

New Member
Joined
Jun 26, 2018
Messages
37

ADVERTISEMENT

Yes just the red X with 400
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,727
Office Version
  1. 365
Platform
  1. Windows
In that case step through the code using F8 & see what happens
 

daltendavis

New Member
Joined
Jun 26, 2018
Messages
37

ADVERTISEMENT

There we go... Run-time error '1004':

Application-defined or object-defined error

happened when I got to the - .Range("E2:O100,R2:AJ100"),Copy Range ("a2")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,727
Office Version
  1. 365
Platform
  1. Windows
You've got a , before Copy instead of a .
 

daltendavis

New Member
Joined
Jun 26, 2018
Messages
37
Sub ExportOrderSheet()
With Sheets("Historical Orders")
Sheets.Add , Sheets(Sheets.Count)
.Range("E2:O100,R2:AJ100").Copy Range("A2")

End With

End Sub

That is what I have
 

Watch MrExcel Video

Forum statistics

Threads
1,122,165
Messages
5,594,622
Members
413,918
Latest member
Mikey_C

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