Prantick Bala
If you want to copy your Excel worksheet to another workbook or to a new workbook with Microsoft Visual Basic Application (VBA), this is the right place for you. In this article, I’ll show you 5 different ways to copy worksheet from a particular workbook to another workbook using VBA.
Suppose, you have the worksheets List-1, List-2, List-3 in a workbook named Source.
Now, you want to Copy these worksheets to a workbook named Destination.
Download Practice Workbook
There are two workbooks, Source
And Destination
5 Ways to Copy Worksheet to Another Workbook Using VBA
1. Copy Single Worksheet to Another Workbook
In the first method, I’ll show you how you copy a single worksheet to another workbook using VBA. You can copy a spreadsheet before the first sheet, before any specific sheet, or at the end of another workbook.
1.1. Before the First Sheet of Another Workbook
To copy worksheet before the first sheet of another workbook, first,
➤ 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. Now,
➤ 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.
After inserting the code,
➤ Click on the Run icon or press F5.
Now, if you open the Destination workbook you’ll see the List-1 worksheet has been copied 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.
➤ Type the name of the worksheet before which you want to copy your current sheet in the place of Sheet 2 of the following code and run the code.
Sub Copy_to_Another_single()
Sheets("List-1").Copy Before:=Workbooks("Destination.xlsx").Sheets("Sheet 2")
End Sub
As a result, 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, you have to insert 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
As a result, the sheet will be copied at the end of the Destination Workbook.
Read More: How to Copy Excel Sheet to Another Sheet (5 Ways)
2. Copy Multiple Sheets to Another Workbook
Now, I’ll show you how you can 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 in the workbook Destination.xlsx.
After that,
➤ Press F5 to run the code.
As a result, the worksheets will be copied in the workbook Destination.xlsx.
Read More: Excel VBA to Copy Multiple Sheets to New Workbook
3. Copy Worksheet to a Closed Workbook
You can also copy the worksheet to a closed workbook. Suppose, you want to copy the sheet named List 1 to the Destination workbook which is not opened. Now,
➤ 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. Here, you have to put the location of the workbook in which you want to copy the sheet. The code will first open the workbook, then will copy the sheet in the workbook and at last, will close the workbook.
After inserting the code,
➤ Press F5 to run the code.
Now, if you open the Destination workbook, you will see the sheet has been copied in this workbook.
Read More: Excel VBA: Copy Worksheet to Another Workbook Without Opening
4. Copy Excel Sheet to a New Workbook
In this method, I’ll show you how you can 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.
After inserting the code,
➤ Press F5 to run the code.
As a result, you will see a new workbook will be opened with the copied sheet.
Read More: How to Copy a Sheet in Excel (5 Ways)
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.
After inserting the code,
➤ Press F5 to run the code.
As a result, you will see a new workbook will be opened with all of the sheets you mentioned in the code.
Read More: How to Copy Multiple Sheets in Excel to New Workbook (3 Methods)
Conclusion
From the methods described in this article, you’ll find ways to copy a single sheet, multiple sheets to a selected workbook or to a new workbook. I hope, now, you will be able to copy worksheet to another workbook using VBA. If you have any confusion about any of the ways of the article please feel free to leave a comment.
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.