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

Question

I am 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 located in the same folder as ActiveWorkBook.
Especially - is there a way to do this without opening test.xls?

Answer 1

You can open a closed book (opening in the background is much easier than working with closed books), and then copy all sheets with Test.xls to a specific part of another book (i.e. before sheet 00 ) in one line

Code below:

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

The code suppressed any warnings, code events in Test.xls and escaping

 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 think it's simple, but if you want to copy all the sheets, you need to copy the whole spreadsheet file. Wouldn't it be easier to programmatically save your "old" book as a new name? You would create a whole new file with the same contents.

Maybe I missed something, but this is another corner to look at it.