How to copy a worksheet using Excel VBA


In this tutorial, we will see how to copy worksheets within the same workbook, copy to a new workbook, copy from another workbook, how to copy multiple sheets and how to copy and rename sheets all using Excel VBA.

Copy Worksheet to a new workbook :
Sub copytonewbook()
ActiveSheet.Copy 'Opens up a new workbook and copies the sheet.
End Sub

The above code creates a copy of the active sheet in the active workbook by opening a new workbook with Excel's default name say "Book1". Then you have to save the new workbook for future purposes.

Copy Worksheet in the same workbook :
Sub justcopysheet()
ActiveSheet.Copy After:=Worksheets(Sheets.Count) 'Copies in the same workbook.
On Error Resume Next
ActiveSheet.Name = "Duplicate" 'Renames the sheet to Duplicate
End Sub

The above code copies the worksheet after all the sheets and renames the sheet to duplicate. On an error, it skips and resumes next.

Copy multiple worksheets :

So far, we have learned how to copy a single worksheet now we will learn how to copy multiple worksheets.

Sub copymultiplesheets()
Sheets(Array("Sheet1", "Sheet2")).Select
ActiveWindow.SelectedSheets.Copy After:=Worksheets(Sheets.Count) 'Copies multiple sheets at last of all the sheets
End Sub

First, we select multiple sheets using VBA code, here am selecting sheet 1 and sheet 2.

Secondly, we copy the selected worksheets in our active workbook and copy after all the worksheets - at last.

Copy to another workbook :
Sub copyto()
Application.DisplayAlerts = False
Dim mainbook As Workbook
Application.ScreenUpdating = False
Set mainbook = Workbooks.Open("C:\Users\Username\Desktop\Main.xlsx")
ThisWorkbook.Sheets("Sheet1").Copy After:=mainbook.Sheets(mainbook.Sheets.Count)
mainbook.Save 'Save the workbook before closing
Application.ScreenUpdating = True
End Sub

At first, we disable display alerts, basically setting display alerts to false disable alert boxes from appearing or popping up.

Secondly, setting screen updating to false actually disables the tasks or process from being visible on the screen, so that they always run in background making it clean. Turning screen updating to true brings back lively updates.

At last, we copy our worksheet from source to the destination workbook. Note that we save and close the opened workbook.

Copy from another workbook :
Sub copyfrom()
Dim mainbook As Workbook
Application.ScreenUpdating = False
Set mainbook = Workbooks.Open("C:\Users\Username\Desktop\Main.xlsx")
Application.ScreenUpdating = True
End Sub

This is the same as the above code, but the only difference is that we copy from some another workbook and paste it in the active workbook.

Hope you liked this article, if so please share it with your friends.

Excel Logo

Download project file

Share on:
Most Popular