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

[daltendavis]

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.

 

[Fluff]

How about

Sub daltendavis()
   With Sheets("Historical Orders")
      Sheets.Add , Sheets(Sheets.Count)
      .Range("E2:O100,R2:AJ100").Copy Range("a2")
   End With
End Sub

 

[daltendavis]

This VBA created a new sheet, however didnt copy the data from sheet-Historical Orders-

 

[Fluff]

Were there any error messages?

 

[daltendavis]

Yes just the red X with 400

 

[Fluff]

In that case step through the code using F8 & see what happens

 

[daltendavis]

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]

You've got a , before Copy instead of a .

 

[daltendavis]

that was a mistype on my part.... sorry about that.

 

[daltendavis]

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