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.
- 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.
- 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.
3. Copy Multiple Sheets to New Workbook by Dragging
For dragging worksheets from one workbook to another, keep both books open.
- 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.
- 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.
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 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 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.
- 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.
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.
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!