Copy all sheets from one (closed) workbook to another "

Question

I'm trying to copy all sheets from one book to another

Windows("test.xls").Sheets.Copy Before:=Workbooks(ThisWorkbook).Sheets("00")

Error - type mismatch !
"test.xls" is in the same folder as ActiveWorkBook.
Especially - is there a way to do this without opening "test.xls" ?

Answer 1

You can open the closed workbook (opening in the background is far easier than working with closed books) and then copy all the sheets from Test.xls to the specific part of the other book (ie before sheet 00) in one line

The code below:

• opens a closed workbook c:\temp\Test.xls")
• copies all the sheets before sheet 00 in the Workbook containing the code
• closes Test.xls

The code suppressed any alerts, code events in Test.xls and screenupdating

Sub CopyAll()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
Set Wb1 = Workbooks.Open("c:\temp\Test.xls")
Set Wb2 = ThisWorkbook
Wb1.Sheets.Copy Before:=Wb2.Sheets("00")
Wb1.Close False
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Sub

Answer 2

Maybe i'm thinking to simple but if you want to copy all sheets, essentialy you are copying the whole spreadsheet file. Would'nt it be easier to programmatically save your 'old' workbook as a new name? You would create a whole new file with the same content.

Maybe i'm missing something but it's another angle to look at it.