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

excel, vba

Solution

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

Problem

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

Original source