Copy worksheets to new workbook - Teach Me?

Hi all, i think its lesson time again, i have answered a post where the asker asked to copy 10 sheets from the open workbook to a new workbook here's the example i gave:

[VBA]

Sub ShtCopy()
Arr = Array("Sheet1", "Sheet2")
On Error Resume Next
Sheets(Arr).Copy = NewWorkbook
ActiveWorkbook.SaveAs "Saved Sheets" & ".xls"
ActiveWorkbook.Close

End Sub

[/VBA]straight forward enough it works but not elegant, i then added a couple if things but didnt post them as below:
[VBA]

Option Explicit
Public Sub ShtCopy()
Dim Arr As Range
Set Arr = Array("Sheet1", "Sheet2")
On Error Resume Next
Sheets(Arr).Copy = NewWorkbook
ActiveWorkbook.SaveAs "Saved Sheets" & ".xls"
ActiveWorkbook.Close
End Sub

[/VBA]now excel has a problem with NewWorkbook, why is that? and instead of using Array i did try using Sheets.Count assigning the count to a variable but then it either opened a new workbook for every sheet or just copied the last sheet in the count! is there a neater way of copying a specified number of sheets to new workbook?

Regards,
Simon

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
Activeworkbook.saveas "Copied Sheet.xls"
Activeworkbook.close

Prasad, thanks for the reply, i have been told (and i no longer do unless i have to) that you do not need to select things in order to manipulate them as selecting slows things down! hence my example only takes the sheets as an array but does not select them. Your code of course will work but would leave the original workbook with grouped sheets, i was looking for an explanation on why NewWorkbook worked in the first example but not in the next!

Regards,
SImon

Hi Simon,

The on error resume next is masking the fact that the use of newworkbook is wrong.

Application.Newworkbook will create a new workbook and return a reference to it. The returned reference would be a workbook object but what you have on the left side of the equals is a sheets method. So it does the copy but fails at the newworkbook.

The .Copy method creates the new workbook automatically but does not return a reference which is why you need to use the activeworkbook object.

The code can be shortened to,
[vba]
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
ActiveWorkbook.Close True, "Copied Sheets.xls"
[/vba]

And in your second code example Arr should be an variant array
[vba] Dim Arr As Variant

Arr = Array("Sheet1", "Sheet2")
[/vba]

I think you need to use Application.NewWorkBook as you have "Option Explicit". Not sure about the other problem...

IGNORE

Thats it Andy, originally when i typed NewWorkbook after the = it capitalized and i thought that it would work and as you rightly pointed out after creating the new workbook the code failed with "Runtime 1004 method of copy failed" but i didnt associate that with the NewWorkbook so i added the dreaded "On Error...." nicely explained thanks!, i will post the neater solution back to the asker.

Kind regards,
Simon

I just checked the help more carefully.
The returned object from application.newworkbook is a NewFile not a Workbook.

Must admit I haven't used the newworkbook approach before.

HaHa! nor me....did it show? its just i used workbooks.add but then that failed because it was trying to copy named sheets to a workbook where the names already existed so i ended up with the original 3 plus the ones i copied - undesireable, so i used newworkbook worked a treat until the failure and cured with the dreaded "OERN".

Thanks for posting back!

Regards,
SImon

P.S i have update the solution posted here http://www.mcse.ms/message2366145.html

Andy, in this example
[VBA]
Public Sub ShtCopy1()
Dim Arr As Variant
Set Arr = Array("Sheet1", "Sheet2")
Sheets(Arr).Copy = Application.NewWorkbook
ActiveWorkbook.SaveAs "Saved Sheets" & ".xls"
ActiveWorkbook.Close
End Sub
[/VBA]i have Arr set as variant not range as previous but i get a "Runtime Error 13, Type Mismatch" at this line

Quote:

---

Set Arr = Array("Sheet1", "Sheet2"

---

)why is that? do i need to use a with statement i.e With this Workbook..?

Drop "Set"