PDA

View Full Version : Solved: Copy more than one sheet to new workbook



mae0429
06-06-2008, 01:04 PM
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:

Sub Sheet_Separator()
Sheets(Array("Sheet1", "Sheet2")).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial
End Sub



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

Thanks!

grichey
06-06-2008, 01:13 PM
This works now
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)

mae0429
06-06-2008, 01:40 PM
For some reason, I keep getting a 'subscript out of range' error when I try to activate my workbook in the following line:


Workbooks("Master File").activate

grichey
06-06-2008, 02:03 PM
is "Master File" the name of your .xls?

mae0429
06-06-2008, 02:04 PM
Yup.

lucas
06-06-2008, 02:33 PM
Maybe:
Option Explicit
Sub CopySheets()
Dim aShtLst As Variant
aShtLst = Array("Sheet1", "Sheet2", "Sheet3")
ThisWorkbook.Sheets(aShtLst).Copy
Application.CutCopyMode = False
End Sub

mae0429
06-06-2008, 02:39 PM
Beautiful, thank you!

4elephants
06-07-2008, 02:01 AM
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.