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.