Solved: Copy more than one sheet to new workbook

[mae0429]

Is there a way to copy more than one sheet (in their entirety) and then add a new workbook and paste them in there?

Something like:
[vba]
Sub Sheet_Separator()
Sheets(Array("Sheet1", "Sheet2")).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial
End Sub

[/vba]

This only copies cell 'A1' on both sheets to sheets 1 and 2 in the new workbook...

Thanks!

[grichey]

This works now
[vba] Workbooks.Add
Workbooks("book1").activate
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

Application.CutCopyMode = False
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy Before:=Workbooks("Book2"). _
Sheets(1)[/vba]

[mae0429]

For some reason, I keep getting a 'subscript out of range' error when I try to activate my workbook in the following line:

[VBA]
Workbooks("Master File").activate
[/VBA]

[grichey]

is "Master File" the name of your .xls?

[mae0429]

Yup.

[lucas]

Maybe:
[VBA]Option Explicit
Sub CopySheets()
Dim aShtLst As Variant
aShtLst = Array("Sheet1", "Sheet2", "Sheet3")
ThisWorkbook.Sheets(aShtLst).Copy
Application.CutCopyMode = False
End Sub[/VBA]

[mae0429]

Beautiful, thank you!

[4elephants]

You know this could be done without VBA, Click on all the tabs to copy while holding down the ctrl key Then Right click one of the tabs, select 'move or copy'. In the 'to book' field select (new book), click the make copy check box, click OK. You then have a new workbook with all the tabs yoiu wanted.