Move or Copy Worksheets VBA

In Excel, you can manually move or copy a worksheet to another workbook by right-clicking the selected sheet and selecting the Move or Copy option, but, if you want to use Excel VBA to move or copy worksheets within a workbook or between workbooks, how to do it?

Move Worksheets VBA

Move the active sheet to the beginning

Sub MoveBeginning()
'Move the active sheet to the beginning
    ActiveSheet.Move Before:=Worksheets(1)
End Sub

You can use to move the Worksheets and use Before argument or After argument to specify where to move the sheet to. In this example, we use Worksheets(1) to point to the first sheet in the workbook, and then move the active sheet before that one.

Move the active sheet to the end

Sub MoveEnd()
'Move the active sheet to the end
    ActiveSheet.Move After:=Worksheets(Worksheets.Count)
End Sub

There is nothing in VBA lets you point to “the last sheet”. But you can find the maximum count of worksheets, and then use that number as an index for the Worksheets object. In this example, you can use Worksheet(Worksheets.Count) to point to the last sheet.

Move Sheet 1 before Sheet 3

Sub MoveBefore()
'Move Sheet 1 before Sheet 3
    Sheets("Sheet1").Move Before:=Sheets("Sheet3")
End Sub

You can move sheets simply by calling them out by sheet name. In this example, we are moving Sheet1 before Sheet3.

Move the active sheet to another Workbook

Sub MoveToSpecificWorkbook()
'Move the active sheet to the beginning of named Workbook.
'Replace YourWorkbook.xls with the full name of the target workbook you want.
    ActiveSheet.Move Before:=Workbooks("YourWorkbook.xls").Sheets(1)
End Sub

In this example, we are moving the active sheet to the beginning of Workbook named YourWorkbook.xls.

Move the active sheet to a new Workbook

Sub MoveToNew()
'Move the active sheet to a new Workbook.
    ActiveSheet.Move
End Sub

Copy Worksheets VBA

Copy the active sheet to a new Workbook

Sub CopyToNew()
'Copy the active sheet to a new Workbook.
    ActiveSheet.Copy
End Sub

You can use to copy the Worksheets and use Before argument or After argument to specify where to copy the sheet to.

Copy the active sheet to another Workbook

Sub CopyToSpecificWorkbook()
'Copy the active sheet to the beginning of named Workbook.
'Replace YourWorkbook.xls with the full name of the target workbook you want.
    ActiveSheet.Copy Before:=Workbooks("YourWorkbook.xls").Sheets(1)
End Sub

If you want to copy the active sheet to the end of another workbook, you can use below Macro code.

Sub CopyToSpecificWorkbook2()
'Copy the active sheet to the end of named Workbook.
'Replace YourWorkbook.xls with the full name of the target workbook you want.
    ActiveSheet.Copy After:=Workbooks("YourWorkbook.xls").Sheets(Workbooks("YourWorkbook.xls").Worksheets.Count)
End Sub

In this example, the VBA code: Workbooks("YourWorkbook.xls").Worksheets.Count count all the sheets of Workbook named YourWorkbook.xls, the VBA code: Workbooks("YourWorkbook.xls").Sheets(Workbooks("YourWorkbook.xls").Worksheets.Count) point to the last sheet of Workbook named YourWorkbook.xls.

Comments

  1. Steve says

    Very helpful. Thanks. The problem I’m having is this: when I click on my macro to copy the sheet after tab “x” that’s fine. but if I click on it again the new worksheet appears BEFORE the active sheet, so it looks like they’re going backward. I can’t figure out a solution to this.

    Thank you for taking the time to read this!

  2. Kare says

    the code fails to run. I suspect its the workbook reference. Does it need the entire path (ex. c:\Users\….)? I tried every combination, just file name, full path and nothing works. any suggestions?

Leave a Reply

Your email address will not be published. Required fields are marked *