Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


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

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.

First Dataset to Copy Multiple Sheets in Excel to New Workbook

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

Second Dataset to Copy Multiple Sheets in Excel to New Workbook

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.

*Note: If the worksheets that 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, then hold down CTRL and click on your desired selections.
  • Right-click the mouse and will see a list of options. Select Move or Copy… from the list.

How to Copy Multiple Sheets in Excel to New Workbook

  • 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 workbook is shown. Click on that.

  • Next, from the To book field, select a 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.

How to Copy Multiple Sheets in Excel to New Workbook


Similar Readings:


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.

How to Copy Multiple Sheets in Excel to New Workbook

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

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.

How to Copy Multiple Sheets in Excel to New Workbook

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

How to Copy Multiple Sheets in Excel to New Workbook

  • Hence, 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.

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

And the destination workbook as workbook2.

  • First, press ALT+F11 to open the Microsoft Visual Basic for Applications 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

 💡 Code Explanation

  1. 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 code here.
  • ReDim Worksheets(2)”: here, 2 is the total number of worksheets that we need to copy.
  • The name of the worksheets that we want to copy: “Data 1” & “Data 2”.
  • Now, Run the code and you will see that the worksheets have been copied to the destination workbook (i.e. Workbook2).

How to Copy Multiple Sheets in Excel to New Workbook


How to Copy Single Sheet to New Workbook in Excel

You have learned how to copy multiple sheets ta o 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.

How to Copy Multiple Sheets in Excel to New 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.


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.

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!


Further Readings

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo