How to Copy and Move a Sheet in Excel using VBA

You can copy and move a sheet in Excel using a VBA code, and in this tutorial, we will learn different ways to do that.

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, sheet1 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 after the first sheet, 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 the “Sheet5” before and after the “Sheet3”.

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

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

Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook

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.

Sheets("Sheet5").Move 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” before “Sheet2” as you can see in the above snapshot.

Copy a Sheet to the New Workbook

When you use the copy method without defining any sheet in the current workbook, VBA opens 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 copy all of them to a new workbook.

Copy a Sheet to the Another Workbook

If you want to copy a sheet to another workbook that is already open then you can mention the workbook name after or before the argument.

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

The above code copies Sheet1 from the active workbook and adds to the before the first sheet of Book1.xlsx that 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 and you don’t need to open it. Look at the below code.

Sub vba_copy_sheet()
Dim mybook As Workbook

Application.ScreenUpdating = False

Set mybook = _
Workbooks.Open _
("C:UsersDellDesktopsamplefile.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 the screen updating so that code can perform the task at the backend.

Copy Sheet from Another Workbook without Opening it

In the same way, you can copy and move a sheet from a closed workbook. Look at the below code.

Sub vba_copy_sheet()
Dim mybook As Workbook

Application.ScreenUpdating = False

Set mybook = _
Workbooks.Open _
("C:UsersDellDesktopsamplefile.xlsx")

mybook.Sheets("mySheet").Copy Before:=Workbooks("Book1.xlsx").Sheets(1)
mybook.Close SaveChanges:=True

Application.ScreenUpdating = True
End Sub