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.
Copy Multiple Sheets to New Workbook with VBA in Excel: Step-by-Step
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.
Read More: How to Copy Worksheet to Another Workbook Using VBA
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.
Read More: Excel VBA: Copy Worksheet to Another Workbook Without Opening
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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 a 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.
VBA code for copying specific worksheets by name to new workbook as values
Thank you for your queries. Let’s change the code a bit. Use the following code to copy the data and paste it as values.
Afterward, you will see result like this.
Hello, can you help me with VBA code to copy worksheets from one workbook to another and not run into the naming conflict?
Dear SHERI
Thank you very much for your inquiry. I appreciate your interest in our article. You can copy worksheets from one workbook to another without mentioning the names of the worksheets. You have to slightly change the VBA code. I have modified the code and provided it here for your convenience.
Keep two things in mind before running the code:
● You must keep both workbooks open.
● You must run the code in the destination workbook.
Paste this code in the VBA Macro Editor of your destination workbook and press the Run button or F5 key to run the code.
If you have any more questions, please let us know in the comment section.
Regards
Md. Abu Sina Ibne Albaruni
Team ExcelDemy
Hi Thank you for this code. I tried running this code to copy 12 worksheets from one workbook to another. But after copying the first two worksheets, I am getting the error Subscript out of range. This is my code
Sub Copy_Multiple_Sheets()
Dim Source As String
Dim Destination As String
Source = “GSTR-3B.xlsx”
Destination = “Load 3B.xlsx”
Dim Worksheets As Variant
ReDim Worksheets(12)
Worksheets(1) = “apr”
Worksheets(2) = “may”
Worksheets(3) = “june”
Worksheets(4) = “Jul”
Worksheets(5) = “Aug”
Worksheets(6) = “Sep”
Worksheets(7) = “oct”
Worksheets(8) = “nov”
Worksheets(9) = “dec”
Worksheets(10) = “jan”
Worksheets(11) = “feb”
Worksheets(12) = “mar”
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
Kindly help me
Dear G BHARATHI PRABHA,
Thanks for your response. I have used exactly the same code for my workbooks and it works perfectly. A little reminder for you that keep both the files in the same folder and keep both the files open. In order to avoid the error Subscript out of range, Try to run the code by keeping both the files open. I hope this will solve your problem.
Regards,
Naimul Hasan Arif