For illustration, we have a sample workbook named “Source” with three worksheets “List-1″, “List-2”, and “List-3”.
We will copy these worksheets to a workbook named “Destination”.
Method 1 – Copy Single Worksheet to Another Workbook
1.1. Before the First Sheet of Another Workbook
To copy the worksheet before the first sheet of another workbook,
➤ Press ALT + F11 to open the VBA window.
In the VBA window,
➤ Click on the Insert tab and select Module.
It will open the Module(Code) window.
➤ Insert the following code in the Module(Code) window,
Sub Copy_to_Another_single()
Sheets("List1").CopyBefore:=Workbooks("Destination.xlsx").Sheets(1)
End SubThe code will copy the worksheet “List-1” before the first sheet of the workbook “Destination.xlsx”.
➤ Click on the Run icon or press F5.
If you open the “Destination” workbook you’ll see the “List-1” worksheet has been inserted before the first sheet of the workbook.
1.2. Before a Specific Sheet of Another Workbook
You can also copy the worksheet before any specific sheet of another workbook.
➤ Open VBA and run the following code. In the code, enter the name of the worksheet (e.g. Sheet2) before which you want to copy your current sheet.
Sub Copy_to_Another_single()
Sheets("List-1").Copy Before:=Workbooks("Destination.xlsx").Sheets("Sheet 2")
End Sub
The sheet will be copied before “Sheet2” of the “Destination” Workbook.
1.3. At the End of Another Workbook
To copy the worksheet at the end of the “Destination” workbook, use the following code instead of the previous code.
Sub Copy_to_Another_single()
Sheets("List-1").Copy After:=Workbooks("Destination.xlsx").Sheets(Sheets.count)
End Sub
The sheet will be copied at the end of the “Destination” Workbook.
Read More:Â How to Copy Sheet to End in Excel Using VBA
Method 2 – Copy Multiple Sheets to Another Workbook
To copy multiple sheets,
➤ Insert the following code in the Module(Code) window,
Sub Copy_to_Another_Multiple()
Sheets(Array("List-1", "List-2", "List-3")).Copy Before:=Workbooks("Destination.xlsx").Sheets(1)
End SubThe code will copy the worksheets “List-1”, “List-2” and “List 3” to the workbook “Destination.xlsx”.
➤ Press F5 to run the code.
The worksheets will be copied into the workbook “Destination.xlsx”.
Method 3 – Copy Worksheet to a Closed Workbook
To copy the sheet named “List 1″ to the “Destination” workbook which is not opened,
➤ Insert the following code in the Module(Code) window,
Sub Copy_to_closed_Workbook()
Dim closedbook As Workbook
Application.ScreenUpdating = False
Set closedbook = _
Workbooks.Open _
("C:\Users\User\Desktop\Destination.xlsx")
Workbooks("Source.xlsx").Sheets("List-1").Copy Before:=closedbook.Sheets(1)
closedbook.Close SaveChanges:=True
Application.ScreenUpdating = True
End SubC:\Users\User\Desktop\Destination.xlsx is the location of the Destination workbook. You have to put the location of the workbook in which you want to copy the sheet. The code will first open the workbook, copy the sheet in the workbook, and close the workbook.
➤ Press F5 to run the code.
The sheet has been copied to workbook Destination.
Method 4 – Copy Excel Sheet to a New Workbook
➤ Insert the following code in the Module(Code) window,
Sub Copy_to_New_Single()
Sheets("List-1").Copy
End SubThe code will copy the worksheet “List-1” to a new workbook.
➤ Press F5 to run the code.
A new workbook will be opened with the copied sheet.
Method 5 – Copy Multiple Worksheets to a New Workbook
You can also copy multiple sheets to a new workbook.
➤ Insert the following code in the Module(Code) window,
Sub Copy_to_Another_Multiple()
Sheets(Array("List-1", "List-2", "List-3")).Copy
End SubThe code will copy the worksheets “List-1”, “List-2” and “List-3” to a new workbook.
➤ Press F5 to run the code.
A new workbook will be opened with all of the sheets mentioned in the code.
Download Practice Workbooks
Source workbook
Destination workbook
Related Articles
- VBA to Copy Excel Worksheet to Another Workbook without Formulas
- VBA to Copy Worksheet to Another Workbook and Rename
- Excel VBA: Copy Worksheet to Another Workbook Without Opening
How to Copy closed-Worksheet to Another closed-Workbook Using VBA ?
I modified your code as below, but I got a error at “Workbooks(“File01.xlsx”).Sheets(“Sheet1″).Copy Before:=closedbook.Sheets(1) ”
Sub VBAcopytest()
Dim closedbook As Workbook
Application.ScreenUpdating = False
Set closedbook = _
Workbooks.Open _
(“C:\Users\willy\OneDrive\dataNAVtest2\File02.xlsm”)
Workbooks(“File01.xlsx”).Sheets(“Sheet1”).Copy Before:=closedbook.Sheets(1)
closedbook.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub
hi Willy,
Make sure the file names are correctly spelled in the line. Check if both the source file and target file name are correct as well as if the directory of the workbook file is correct too.
If the problem still persists, please provide us with a bit of more information, like what is the error message or perhaps a screenshot.
I am looking for a way to create a copy of a worksheet that will automatically update in a new workbook as it receives form responses but only if the response contains a certain category. Is this possible?
Hi BZ, thanks for your response. You can reference the cells from one sheet to another workbook. That way, you can automatically update data in the new workbook when you make a change in the existing worksheet. And to update with condition, you need to use conditional function like the IF, AND, OR, EXACT etc. functions.