Excel VBA to Copy Multiple Sheets to New Workbook

Written by Rifat Hassan

Last updated: Dec 5, 2023

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.

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.

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.

Download Practice Workbook

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

Copy Multiple Sheets.xlsm

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.

Further Readings

Get FREE Advanced Excel Exercises with Solutions!

Sub CopyWorksheetsToNewWorkbook() Dim srcWorkbook As Workbook Dim newWorkbook As Workbook Dim srcWorksheet As Worksheet Dim newWorksheet As Worksheet ' Set the source workbook Set srcWorkbook = ThisWorkbook ' Create a new workbook Set newWorkbook = Workbooks.Add ' Loop through each worksheet in the source workbook For Each srcWorksheet In srcWorkbook.Worksheets ' Copy only values to the new workbook srcWorksheet.UsedRange.Value = srcWorksheet.UsedRange.Value ' Copy the data to the new workbook srcWorksheet.Copy After:=newWorkbook.Sheets(newWorkbook.Sheets.Count) Set newWorksheet = newWorkbook.Sheets(newWorkbook.Sheets.Count) newWorksheet.Name = srcWorksheet.Name newWorksheet.Cells.Copy newWorksheet.Cells.PasteSpecial xlPasteValues Application.CutCopyMode = False Next srcWorksheet ' Save and close the new workbook ' Replace "C:\Path\To\Save\NewWorkbook.xlsx" with your desired file path and name newWorkbook.SaveAs "C:\ExcelDemy\NewWorkbook.xlsx" newWorkbook.Close ' Clean up Set newWorksheet = Nothing Set newWorkbook = Nothing Set srcWorksheet = Nothing Set srcWorkbook = Nothing End Sub

Sub Copy_Worksheets() 'variable declaration Dim Source As Workbook Dim Destination As Workbook 'set source file Set Source = Workbooks("Source.xlsx") 'set destination file Set Destination = Workbooks("Destination.xlsm") 'copy worksheets from source file to destination file Dim i As Long For i = 1 To Source.Sheets.Count Source.Sheets(i).Copy After:=Destination.Sheets(Destination.Sheets.Count) Next i End Sub

Sub CopySheets() Dim Source As String Dim Destination As Workbook Source = "C:\Users\PC 50\Downloads\SOURCE.xlsx" Set Destination = ThisWorkbook Dim Worksheets As Variant ReDim Worksheets(3) Worksheets(1) = "Sheet1" Worksheets(2) = "Sheet2" Worksheets(3) = "Sheet3" Dim i As Variant Dim rng As Range For i = 1 To UBound(Worksheets) Set rng = Workbooks.Open(Source).Sheets(Worksheets(i)).Range("A1:D11") rng.Copy Destination:=Destination.Sheets(Worksheets(i)).Range("A1") Next i End Sub

8 Comments

Narasimhan Jun 2, 2023 at 5:45 PM

VBA code for copying specific worksheets by name to new workbook as values

Mehedi Hasan Shimul Jun 4, 2023 at 4:01 PM

Hi NARASIMHAN!

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.

Sheri Jun 29, 2023 at 12:20 AM

Hello, can you help me with VBA code to copy worksheets from one workbook to another and not run into the naming conflict?
- Reply
  Md. Abu Sina Ibne Albaruni Jul 2, 2023 at 12:54 PM
  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.
```
Sub Copy_Worksheets()
    
    'variable declaration
    Dim Source As Workbook
    Dim Destination As Workbook
    
    'set source file
    Set Source = Workbooks("Source.xlsx")
    
    'set destination file
    Set Destination = Workbooks("Destination.xlsm")
    
    'copy worksheets from source file to destination file
    Dim i As Long
    For i = 1 To Source.Sheets.Count
        Source.Sheets(i).Copy After:=Destination.Sheets(Destination.Sheets.Count)
    Next i
    
End Sub
```
  If you have any more questions, please let us know in the comment section.
  
  Regards
  Md. Abu Sina Ibne Albaruni
  Team ExcelDemy
G BHARATHI PRABHA Jun 30, 2023 at 4:04 PM

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
- Reply
  Naimul Hasan Arif Jul 2, 2023 at 2:30 PM
  
  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
dory189 Feb 9, 2024 at 6:30 PM

Hi, I found your code very useful just one question if I would like to copy only a specific range from the source workbook, how do i insert that into the code?
- Reply
  Lutfor Rahman Shimanto Feb 11, 2024 at 1:08 PM
  Hello DORY
  
  Thanks a Ton! for your nice words. Your appreciation means a lot to us.
  
  You would like to copy only a specific range from the source workbook. I am presenting an Excel VBA Sub-procedure that will fulfil your requirements.
  
  OUTPUT OVERVIEW:
  
  Excel VBA Sub-procedure:
```
Sub CopySheets()
    
    Dim Source As String
    Dim Destination As Workbook
    
    Source = "C:\Users\PC 50\Downloads\SOURCE.xlsx"
    Set Destination = ThisWorkbook
    
    Dim Worksheets As Variant
    ReDim Worksheets(3)
    
    Worksheets(1) = "Sheet1"
    Worksheets(2) = "Sheet2"
    Worksheets(3) = "Sheet3"
    
    Dim i As Variant
    Dim rng As Range
    
    For i = 1 To UBound(Worksheets)
        Set rng = Workbooks.Open(Source).Sheets(Worksheets(i)).Range("A1:D11")
        rng.Copy Destination:=Destination.Sheets(Worksheets(i)).Range("A1")
    Next i
    
End Sub
```
  Hopefully, you will like the example and the sub-procedure. Good luck.
  
  Regards
  Lutfor Rahman Shimanto
  Excel & VBA Developer
  ExcelDemy