Copy worksheets to new workbook - Teach Me?

[Simon Lloyd]

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:


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

End Sub
straight forward enough it works but not elegant, i then added a couple if things but didnt post them as below:

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

[Prasad_Joshi]

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

[Simon Lloyd]

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

[Andy Pope]

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,

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
ActiveWorkbook.Close True, "Copied Sheets.xls"


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

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

[moa]

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

IGNORE

[Simon Lloyd]

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

[Andy Pope]

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.

[Simon Lloyd]

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

[Simon Lloyd]

Andy, in this example

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
i have Arr set as variant not range as previous but i get a "Runtime Error 13, Type Mismatch" at this line
Set Arr = Array("Sheet1", "Sheet2")why is that? do i need to use a with statement i.e With this Workbook..?

[moa]

Drop "Set"