Excel VBA to Copy Multiple Sheets to New Workbook

Get FREE Advanced Excel Exercises with Solutions!

We often have to copy one or multiple sheets from one workbook to a new workbook while working in Microsoft Excel. In this article, I am going to explain the whole procedure in detailed steps on how to copy multiple sheets to a new workbook with VBA in Excel. I hope it will be very helpful for you if you are looking for an efficient way to do so.


Download Practice Workbook

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


Step-by-Step Procedures to Copy Multiple Sheets to New Workbook with VBA in Excel

In order to offload the repetition of creating the same worksheets again in a newly created workbook, we can apply the VBA code to do so. The process is discussed in detail in the following section.


Step 1: Create a Workbook with Multiple Sheets

  • In order to copy multiple sheets from a workbook, it is the first and foremost task to have a complete workbook with multiple sheets. Here, I have a workbook named Workbook1 with employees’ sales performance in different months in sheets January, February, and March.

Copy Multiple Sheets to New Workbook VBA

  • Now, create a new workbook and place both workbooks in the same folder. Here, I have created another workbook named Copy Multiple Sheets to New Workbook VBA and placed both workbooks under the same folder.


Step 2: Insert Module to Copy Multiple Sheets to New Workbook

  • Now, open the workbook where you want to copy multiple sheets.
  • Followingly, go to the Developer tab and click on Visual Basic from the ribbon.

Create Module to Copy Multiple Sheets to New Workbook with VBA

  • After that, click on the Insert tab.
  • From the available options, pick Module.


Step 3: Execute VBA to Copy Multiple Sheets to New Workbook

  • Now, insert the following VBA code in that module and click on Run to execute the code. Alternatively, you can press the  F5  button to execute the code.

Sub Copy_Sheets()
Dim Source As String
Dim Destination As String
Source = "Workbook1.xlsx"
Destination = "Copy Multiple Sheets to New Workbook VBA.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

Execute VBA to Copy Multiple Sheets to New Workbook

  • Thus, we can copy multiple sheets to a new workbook with VBA.

Copy Multiple Sheets to New Workbook VBA

Notes
  • For this method to work perfectly, you must keep both 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.

Similar Readings:


Conclusion

At the end of this article, I like to add that I have tried to explain the whole procedure in detailed steps on how to copy multiple sheets to new workbook with VBA in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.


Further Readings

Rifat Hassan

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 the 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo