Excel VBA – Copy Sheet To Another Workbook

Copy Worksheet to another Excel file

VBA Command: Worksheet.copy

You are working in an Excel file. You want to copy one of the sheets in the current workbook to a new workbook or a already existing Excel file.

You can use this VBA code to copy a sheet from one workbook to another.

Sub Copy_Sheet_To_Workbook(iSh As Worksheet, oWb As Workbook)
    Dim sh As Worksheet
    
    'Intimate if any sheet with same name exists
    For Each sh In oWb.Worksheets
        If VBA.LCase(sh.Name) = VBA.LCase(iSh.Name) Then
            MsgBox "Sheet with Same name already exists"
            Exit For
        End If
    Next
    
    'Copy to Report to Input Sheet
    iSh.Copy after:=oWb.Sheets(oWb.Sheets.Count)
    oWb.Save
End Sub

The above code will copy the worksheet to other workbook to end of the sheet.

If you would like to rename it, you can do so by using Activesheet property. Because the sheet that got copied will be in focus by default.
Activesheet.name = “CopiedSheet”

Also, You can access this sheet again with same active sheet object like this.

Set nSheet = activesheet

nSheet.range(“A1”) = 1

So, Copying a sheet from one workbook to another is not that tough. The above code is ready to use. Just copy and use it in your vba module.