Copy Worksheets to New Book

[Nigel]

I recoreded a macro and it worked fine, I then copied the coed into a
function and it fails here is the code

ActiveWorkbook.Sheets(Array("Airgas", "Praxair", "Other G1 Cust", "T1 Cust",
"Other Cust")).Select
ActiveWorkbook.Sheets("Other Cust").Activate

ActiveWorkbook.Sheets(Array("Airgas", "Praxair", "Other G1 Cust", "T1
Cust", "Other Cust")).Copy Before:=Workbooks(NEWWORKSHEET).Sheets(11)
application.Run Range("ScOnWindow")
ActiveWorkbook.Sheets(Array("Airgas", "Praxair", "Other G1 Cust", "T1
Cust", "Other Cust")).Select
ActiveWorkbook.Sheets("Airgas").Activate
Range("A1").Select
ActiveWorkbook.Sheets("Price SRP1 by Req Date").Select

it fails on this line
ActiveWorkbook.Sheets(Array("Airgas", "Praxair", "Other G1 Cust", "T1
Cust", "Other Cust")).Copy Before:=Workbooks(NEWWORKSHEET).Sheets(11)

with the error message subscript out of range. (NEWWORKSHEET is the name of
the book the sheets are being copied to and is declared earlier in the code)

Any solutions would be helpful

thanks in advance

 

[Patrick Molloy]

you're trying to copy before sheet 11. Are ther 11 sheets in the target
workbook already?

maybe replace
Before:=Workbooks(NEWWORKSHEET).Sheets(11)

with

Before:=Workbooks(NEWWORKSHEET).Sheets(
Workbooks(NEWWORKSHEET).worksheets.count)