How to Copy Multiple Sheets to New Workbook in Excel (4 Ways)

Excel plays a great role for professionals. Excel provides lots of functions and formulas for different problems. But for some problems, there are no functions or formulas. We solve these problems using different features and commands. Here we will describe how to copy multiple sheets in Excel to a new workbook. No exact formula exists in Excel for this problem.

How to Copy Multiple Sheets to New Workbook in Excel: 4 Unique Methods

Here, we will have a data set of two sheets for presentation purposes. Data on these sheets are not very significant. We introduced that data so that you can understand it easily.

Let’s say, we have two worksheets about some employees of an organization. One worksheet is named “Data 1” and describes simple information about the employees like ID, Name, and Email.

Another is named “Data 2” which describes the performance of those employees.

We want to copy these two sheets to a new workbook. In order to copy multiple sheets to a new workbook in Excel, we will describe four unique ways with proper illustrations.

1. Apply Move or Copy Command

We can copy multiple sheets in Excel using the Move or Copy command of Excel. Check out the steps below.

📌 Steps:

• First, select the worksheets of your source workbook. We have 2 sheets in our Dataset named “Data 1” and “Data 2”.

• Right-click the mouse and will see a list of options. Select Move or Copy… from the list.

• Now, a pop-up will appear called Move or Copy. Under the To book section, you will find a dropdown sign. Here, the list of the opened workbooks is shown. Click on that.

• Next, from the To book field, select (new book).
• Then, mark the Create a copy. Now, press OK.

• We will get a new workbook named “Book2” (or “Book1”) with our previous data being copied into that workbook.

2. Copy Multiple Sheets to New Workbook from Ribbon

We have the same dataset and still we want to copy multiple sheets to a new workbook. But this time we will do this task from the Excel ribbon.

📌 Steps:

• First, select sheets “Data 1” and “Data 2” using the CTRL button.

• Now, go to Home from the ribbon.
• Then go to Format from the ribbon and will see a drop-down.
• Select Move or Copy Sheet… from the drop-down list.

• A pop-up will appear called Move or Copy just like method 1. After that, repeat just these steps of method 1 to serve your purpose.

Read More: Copy Worksheet to Another Workbook without Reference in Excel

3. Copy Multiple Sheets to New Workbook by Dragging

For dragging worksheets from one workbook to another, keep both books open.

📌 Steps:

• First, open the source and target workbooks.
• Then, arrange the two workbooks horizontally.
• From the source workbook, select the worksheets (i.e. “Data 1” & “Data 2”) by pressing CTRL.

• Now, hold down the CTRL key and drag the sheet to the target workbook.

• Hence, you will see that the new sheets have been added to the destination workbook (i.e. “Copied Result Workbook”).

4. Use of VBA Code

Let’s serve the same purpose with a bit different approach. Now we will learn to copy multiple sheets to new workbook with VBA code. The procedure for running the VBA code is as simple as below.

📌 Steps:

• Now, we have named the source workbook “Workbook1”.

And the destination workbook is “Workbook2”.

• First, press ALT + F11 to open the Visual Basic window.
• Here, click Insert and select Module.

• Now, the module window will appear on the screen. Insert the following code here.

Code:

Sub Copy_Multiple_Sheets()

Dim Source As String
Dim Destination As String

Source = "Workbook1.xlsx"
Destination = "Workbook2.xlsm"

Dim Worksheets As Variant
ReDim Worksheets(2)

Worksheets(1) = "Data 1"
Worksheets(2) = "Data 2"

Dim i As Variant
For i = 1 To UBound(Worksheets)
    Workbooks(Source).Sheets(Worksheets(i)).Copy _
        After:=Workbooks(Destination).Sheets(Workbooks(Destination).Sheets.Count)
Next i

End Sub

• Now, Run the code and you will see that the worksheets have been copied to the destination workbook (i.e. “Workbook2”).

Read More: Excel VBA to Copy and Rename a Worksheet Multiple Times

How to Copy Single Sheet to New Workbook in Excel

You have learned how to copy multiple sheets to a new workbook. The method of copying a single sheet is similar to methods: 1, 2, or 3. You can choose any of those methods. Let’s learn a newer one to copy a single sheet.

📌 Steps:

• First, create a destination workbook (i.e. “Workbook3”).

• Now, go to the worksheet that you want to copy from the source workbook (i.e. “Workbook1”).
• Here, press CTRL + A and then CTRL + C to copy the whole workbook.

• Now, go to the destination workbook (i.e. “Workbook3”).
• Here, select the first cell A1, and press CTRL + V to paste the copied data.

• Hence you will see that the sheet has been copied to the destination workbook.

Read More: How to Copy Sheet to Another Workbook with Excel Formulas

Things to Remember

• While dragging a sheet from one workbook to another, don’t forget to press the CTRL key. Otherwise, the data will be cleared from the source worksheet.

Download Practice Workbook

Download this practice sheet to exercise while you are reading this article.

Conclusion

Here we described four methods to copy multiple sheets to a new workbook. We showed the easiest methods that you can catch easily. Don’t forget to share your feelings. We are always responsive to your queries!

Related Articles

• How Do I Duplicate a Sheet Multiple Times in Excel
• How to Copy Excel Sheet into Word
• [Fixed!] Move or Copy Sheet Not Working in Excel
• How to Copy a Sheet to Another Sheet in Excel
• How to Copy Excel Sheet to Another Sheet with Same Format

<< Go Back to Copy Sheet | Worksheets | Learn Excel