Excel VBA to Copy Multiple Sheets to New Workbook

We often have to copy one or multiple sheets from one workbook to a new workbook while working in Excel. Today I’ll show you how you can copy multiple sheets from one workbook to a new workbook using VBA in Excel.


Download Practice Workbook


How to Use VBA Codes to Copy Multiple Sheets To New Workbook

Here I’ve got two workbooks named Workbook1 and Workbook2.

Workbook1 has 5 worksheets called “January”, “February”, “March”, “April” and “May” that contain the sales records of 5 months of a company called Sunflower group.

Data Set to Copy Multiple Sheets to New Workbook through VBA

Our objective today is to copy multiple worksheets from this workbook called “Workbook1” to the destination worksheet “Workbook2”.

Step 1:

Open both the source workbook and the destination workbook (They must be placed in the same folder of your PC).

Go to the destination workbook (“Workbook2” in this example) and press ALT+F11 on your keyboard. The VBA window will open.

VBA Window to Copy Multiple Sheets to New Workbook through VBA

Step 2:

Go to the Insert tab in the VBA window. 

From the options available, select Module.

Inserting Module to Copy Multiple Sheets to New Workbook through VBA

Step 3:

A new module window called “Module 1” will open. 

Insert the following VBA codes in the module.

Code:

Sub Copy_Sheets()

Dim Source As String
Dim Destination As String

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

Dim Worksheets As Variant
ReDim Worksheets(3)

Worksheets(1) = "January"
Worksheets(2) = "February"
Worksheets(3) = "March"

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

Notes:

  • Here in the 4th line Source is the name of your source workbook. It is “Workbook1.xlsx” in this example. You put your one.
  • Then in the 5th line Destination is the name of your destination workbook. It is “Workbook2.xlsm” in this example. You put your one.
  • Keep the extension of the destination workbook as .xlsm, not .xlsx.
  • In the 7th line ReDim Worksheets(3), 3 is the total number of worksheets that I want to copy. You use your one.
  • Finally, in the 8th, 9th, and 10th lines, use the names of the worksheets that you want to copy (I want to copy worksheets called “January”, “February”, and “March”).

VBA Code to Copy Multiple Sheets to New Workbook through VBA

Step 4:

Save the workbook as Excel Macro-Enabled Workbook.

Saving Macro File to Copy Multiple Sheets to New Workbook through VBA

Step 5:

Come back to your destination workbook (“Workbook2” in this example).

Press ALT+F8 on your keyboard. A dialogue box called Macro will open. Select Copy_Sheets and click on Run.

Running Macro to Copy Multiple Sheets to New Workbook through VBA

All the worksheets that you want to copy will be copied from the source workbook to the destination workbook.

Copy Multiple Sheets to New Workbook through VBA

Special Notes:

  • For this method to work perfectly, you must keep both the workbooks open at the same time. Unless this method won’t work.
  • You must write the VBA code and run it in the destination workbook, not in the source workbook.

Alternative Method To Copy Multiple Sheets To New Workbook

The VBA method described above works quite fine, but still, if you don’t want to go through the critical steps of VBA and Macro, then you can use another suitable method described below.

It will copy multiple sheets from one workbook to another new workbook manually.

Step 1:

Open both the source workbook and the destination workbook (They must be placed in the same folder of your PC).

Go to the source workbook and take your mouse cursor to the worksheet name that you want to copy (“January” in this example).

Moving Cursor to Copy Multiple Sheets to New Workbook through VBA

Step 2:

Right-click on the worksheet name.

From the options available, select Move or Copy.

Options to Copy Multiple Sheets to New Workbook through VBA

Step 3:

A dialogue box called Move or Copy will open. From the To book option, select the name of your destination workbook (“Workbook2” in this example).

Then from the Before sheet option, select move to end. And put a tick on the Create a copy option (This is very important. Unless the sheet will be lost from the source workbook).

Move or Copy Dialogue Box to Copy Multiple Sheets to New Workbook through VBA

Step 4:

Then press OK. The worksheet will be copied from the source workbook to the destination workbook.

Copy Multiple Sheets to New Workbook through VBA

Repeat this process for all the workbooks that you want to copy.


Conclusion

Using these methods, you can copy multiple worksheets to a new workbook in Excel. Do you know any other method? Or do you have any questions? Feel free to ask us.


Related Readings

How to Copy Excel Sheet with Formulas to Another Workbook (5 Ways)

Hide and Unhide Excel Worksheets from a Workbook

Transfer Data from One Excel Worksheet to Another Automatically

How to Pull Values From Another Worksheet in Excel

How to Reference Worksheet Name in Formula in Excel (3 Easy Ways)

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo