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.
And 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 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.
Read More: [Fixed!] Move or Copy Sheet Not Working in Excel
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.
Read More: VBA to Copy Excel Worksheet to Another Workbook without Formulas
- Excel VBA to Copy and Rename a Worksheet Multiple Times
- How to Copy Sheet with VBA in Excel (13 Quick Ways)
- Excel Copy Worksheet to Another Workbook Without Reference
- How to Copy a Sheet to Another Sheet in Excel (5 Easy Ways)
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).
Read More: How to Copy Excel Sheet to Another Sheet with Same Format
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 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.
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 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).
Read More: Excel VBA: Copy Worksheet to Another Workbook Without Opening
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.
Read More: How to Copy a Sheet in Excel (5 Easy Ways)
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.
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!