How to Copy Worksheet to Another Workbook Using VBA

If you want to copy your Excel worksheet to another workbook or to a new workbook with Microsoft Visual Basic Application, this is the right place for you. In this article, I’ll show you 5 different ways to copy a worksheet from a particular workbook to another workbook using VBA.

Suppose, you have the worksheets “List-1″, “List-2”, and “List-3” in a workbook named “Source”.

Now, you want to Copy these worksheets to a workbook named “Destination”.

Copy Worksheet to Another Workbook Using VBA in Excel: 5 Ways

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 the 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 Sub

The 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 “Sheet2” 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 Sheet to End in Excel Using VBA

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 Sub

The 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 into the workbook “Destination.xlsx”.

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 Sub

C:\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 into this workbook.

4. Copy Excel Sheet to a New Workbook

In this method, I’ll show you how you can copy an 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 Sub

The 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.

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 Sub

The 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.

Download Practice Workbook

There are two workbooks, Source

And Destination

Conclusion

From the methods described in this article, you’ll find ways to copy a single sheet, or multiple sheets to a selected workbook or to a new workbook. I hope, now, you will be able to copy the 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.

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