How to Copy and Move a Sheet in Excel using VBA

Copy a Sheet within the Same Workbook

If you want to copy a sheet within the same workbook, you can use the following code, where you have used the Copy method.

Sheets("Sheet5").Copy Before:=Sheets(1)

In this code, Sheets(1) is the first sheet in the workbook, and when you run this code, it copies the sheet with the name “Sheet5” before the first sheet in the workbook.

And if you want to copy it after the first sheet, then the code would be like the following.

Sheets("Sheet5").Copy After:=Sheets(1)

And if you want to copy it before or after a specific sheet, like “Sheet3”, then the code would be like the following.

Sheets("Sheet5").Copy Before:=Sheets("Sheet3")
Sheets("Sheet5").Copy After:=Sheets("Sheet3")

When you run this VBA code, it copies “Sheet5” before and after “Sheet3” (one copy for each line).

With the following line of code, you can copy a sheet after the last sheet in the workbook. It uses Sheets.Count to get the count of the sheets and then uses that number to copy the sheet to the end.

Sheets("myNewSheet").Copy After:=Sheets(Sheets.Count)

Copy a Sheet in the Same Workbook with New Name

And if you want to copy a sheet and then want to rename it instantly, you need to use the Name property, just like the following code. The copy becomes the active sheet, so ActiveSheet.Name renames the copy.

Sheets("Sheet5").Copy Before:=Sheets(1)
ActiveSheet.Name = "myNewSheet"

Move a Sheet within the Same Workbook

To move a sheet in the same workbook, you need to use the Move method, as you have in the following code.

Sheets("Sheet5").Move After:=Sheets(1)

This code will move “Sheet5” after the first sheet, which puts it before “Sheet2”, as you can see in the above snapshot.

Copy a Sheet to the New Workbook

When you use the Copy method without the Before or After argument, VBA creates a new workbook and copies that sheet to that workbook.

Sheets("Sheet5").Copy

And to move a sheet to a new workbook:

Sheets("Sheet5").Move

Copy Multiple Sheets to the New Workbook

If you want to add multiple sheets to the new workbook in one go, then use code like the following.

Sheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")).Copy

The above code creates an array of the sheets and then copies all of them to a new workbook.

Copy a Sheet to Another Workbook

If you want to copy a sheet to another workbook that is already open, then you can mention the workbook name in the Before or After argument.

Sheets("Sheet1").Copy Before:=Workbooks("Book1.xlsx").Sheets(1)

The above code copies Sheet1 from the active workbook and adds it before the first sheet of Book1.xlsx, which is already open.

And if you want to move it:

Sheets("Sheet1").Move Before:=Workbooks("Book1.xlsx").Sheets(1)

Copy a Sheet to a Closed Workbook

There’s a way that you can copy a sheet to a workbook that is closed without opening it yourself. The code opens it in the background, copies the sheet, saves it, and closes it. Look at the below code.

Sub vba_copy_sheet()
Dim mybook As Workbook
Application.ScreenUpdating = False
Set mybook = _
Workbooks.Open _
("C:\Users\Dell\Desktop\samplefile.xlsx")
Workbooks("Book1.xlsx").Sheets("Sheet2").Copy Before:=mybook.Sheets(1)
mybook.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub

In this code, you have the myBook variable and then a workbook assigned to it. We have turned off screen updating so that the code can perform the task in the background. Change the file path to the location of your file.

Copy Sheet from Another Workbook without Opening it Yourself

In the same way, you can copy and move a sheet from a closed workbook. This code also opens the file in the background, copies the sheet, and closes the file again. Look at the below code.

Sub vba_copy_sheet()
Dim mybook As Workbook
Application.ScreenUpdating = False
Set mybook = _
Workbooks.Open _
("C:\Users\Dell\Desktop\samplefile.xlsx")
mybook.Sheets("mySheet").Copy Before:=Workbooks("Book1.xlsx").Sheets(1)
mybook.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub

Copy a Sheet Multiple Times

To make several copies of a sheet, use a loop. The following code makes five copies of “Template” at the end of the workbook and names them Week 1 to Week 5:

Sub copy_sheet_multiple_times()
    Dim i As Long
    For i = 1 To 5
        Sheets("Template").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Week " & i
    Next i
End Sub

Copy a Sheet to a New Workbook and Save It

After you copy a sheet to a new workbook, that new workbook is the active workbook, so you can save and close it right away:

Sub copy_sheet_and_save()
    Sheets("Sheet5").Copy
    ActiveWorkbook.SaveAs Filename:="C:\Users\Dell\Desktop\Sheet5.xlsx", FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close SaveChanges:=False
End Sub

Frequently Asked Questions