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

dataset

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

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.

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.

code

After inserting the code,

➤ Click on the Run icon or press F5.

code

Now, if you open the Destination workbook you’ll see the List-1 worksheet has been copied before the first sheet of the workbook.

copy worksheet to another workbook vba


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

code

As a result, the sheet will be copied before Sheet2 of the Destination Workbook.

copy worksheet to another workbook vba


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

code

As a result, the sheet will be copied at the end of the Destination Workbook.

copy worksheet to another workbook vba

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 Sub

The code will copy the worksheets List-1, List-2 and List 3 in the workbook Destination.xlsx.

copy worksheet to another workbook vba

After that,

➤ Press F5 to run the code.

As a result, the worksheets will be copied in the workbook Destination.xlsx.

copy worksheet to another workbook vba

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

copy worksheet to another workbook vba

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.

copy worksheet to another workbook vba

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 Sub

The code will copy the worksheet List-1 to a new workbook.

copy worksheet to another workbook vba

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.

copy worksheet to another workbook vba

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 Sub

The code will copy the worksheets List-1, List-2 and List-3 to a new workbook.

copy worksheet to another workbook vba

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.

copy worksheet to another workbook vba

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.


Related Articles

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

4 Comments
  1. 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.

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

Leave a reply

ExcelDemy
Logo