Copy all sheets from one (closed) workbook to another "
Solution 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
Solution 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.
Related videos on Youtube
Alegro
Updated on August 05, 2022Comments
-
Alegro over 1 year
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" ? -
Alegro over 11 yearsExcellent, ThankYou for your StepByStep explanation end for your efforts about coding. THIS IS FOR ADMINS: You should change some rules. Some answers deserve to vote more than just once.