Rifat Hassan
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.
- 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.
- 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
- Thus, we can copy multiple sheets to a new workbook with VBA.
- 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.
