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

Method 1 – Applying Move or Copy Command

Steps:

Select the worksheets of your source workbook. Our dataset has two sheets, “Data 1” and “Data 2.”

*Note: If the worksheets you plan to select are adjacent, then holding SHIFT down and selecting the first and last worksheet will allow you to select them all. If your worksheets are non-adjacent, hold down CTRL and click on your desired selections.

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

A pop-up called Move or Copy will appear. Under the To Book section, you will find a dropdown sign. Click on that to see a list of the opened workbooks.

From the To book field, select (new book).
Mark the Create a copy.
Press OK.

We will get a new workbook named “Book2” (or “Book1”), and our previous data will be copied into it.

Method 2 – Copying Multiple Sheets to the New Workbook from Ribbon

Steps:

Select sheets “Data 1” and “Data 2” using the CTRL button.

Go to Home from the ribbon.
Go to Format from the ribbon, and you will see a drop-down.
Select Move or Copy Sheet… from the drop-down list.

A pop-up will appear called Move or Copy.
Repeat these steps of method 1 to serve your purpose.

Method 3 – Copying Multiple Sheets to the New Workbook by Dragging

Steps:

Open the source and target workbooks.
Arrange the two workbooks horizontally.
From the source workbook, select the worksheets (i.e. “Data 1” & “Data 2”) by pressing CTRL.

Hold down the CTRL key and drag the sheet to the target workbook.

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

Keep in Mind: If you don’t hold the CTRL key while dragging, the sheets will be moved to the destination worksheet.

Method 4 – Using a VBA Code

Steps:

We have named the source workbook “Workbook1”.

And the destination workbook is “Workbook2”.

Press ALT + F11 to open the Visual Basic window.
Click Insert and select Module.

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

Code Explanation:

Here, the source workbook is declared as “Source” and the target workbook is declared as “Destination”.

The extension of the destination workbook is (.xlsm), as we have applied the code here.
“ReDim Worksheets(2)”: Here, 2 is the total number of worksheets we need to copy.
The name of the worksheets that we want to copy: “Data 1” & “Data 2”.

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

How to Copy a Single Sheet to a New Workbook in Excel

Steps:

Create a destination workbook (i.e., “Workbook3”).

Go to the worksheet you want to copy from the source workbook (i.e., “Workbook1”).
Press CTRL + A and then CTRL + C to copy the whole workbook.

Go to the destination workbook (i.e., “Workbook3”).
Select the first cell A1, and press CTRL + V to paste the copied data.

You will see that the sheet has been copied to the destination workbook.

Things to Remember

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

Download the Practice Workbook

Download this sheet to practice.

Copy Sheets to New Workbook (Workbook1).xlsx

Copied Result Workbook (Workbook2).xlsm