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. Make sure to get the developer tab on the ribbon and open the VB editor to write these macros.

Copy a Sheet within the Same Workbook

If you want to copy and 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 code would be like following.

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

And if you want to copy it after the first sheet, then 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 use the number to copy the sheet to the last.

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

Copy a Sheet in the Same Workbook with New Name

And if you want to copy and 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 with 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 the “Sheet5” before the “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 copy 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 following.

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

The above code creates an array of the sheets and then copy all 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 in the after or before the argument.

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

The above code copies the Sheet1 from the active workbook and adds to the before the first sheet of the 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:\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 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 workbook that is close. 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

More Tutorials on Worksheets