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