Excel VBA to Copy Multiple Sheets to New Workbook

Copying multiple sheets from one workbook to another manually is prone to errors and can become tedious with lots of sheets. In this article, we will provide detailed instructions on how to copy multiple sheets to a new workbook automatically using VBA code.

Step 1 – Create a Workbook with Multiple Sheets

In order to copy multiple sheets from a workbook, we’ll of course need a complete workbook with multiple sheets to copy from. Suppose we have a workbook named “Workbook1” containing some employees’ sales performance in different months in sheets “January”, “February”, and “March”.

Create a new workbook and place both workbooks in the same folder.

Here, we created another workbook named “Copy Multiple Sheets to New Workbook VBA” in the same folder as “Workbook1”.

Step 2 – Insert Module to Enter VBA Code

Open the destination workbook (where the sheets will be copied to).
Go to the Developer tab and click on Visual Basic.

Click on Insert.
Select Module.

A Module window opens.

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

Insert the following VBA code in the Module and click on Run (or press the F5 key) 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

The sheets are copied to the new workbook.

Notes

For this method to work, both workbooks must be open when running the code.
Write the VBA code and run it in the destination workbook, not in the source workbook.

Download Practice Workbook

Copy Multiple Sheets.xlsm

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.

Copy Multiple Sheets to New Workbook as values with VBA - Excel

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

Excel VBA to Copy Multiple Sheets to New Workbook

Step 1 – Create a Workbook with Multiple Sheets

Step 2 – Insert Module to Enter VBA Code

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

Further Reading

Leave a reply Cancel reply