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 Move method 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 Copy method 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.

Leave a comment

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

Format your code: <pre><code class="language-vba">place your code here</code></pre>

6 comments
  1. NA
    Nathalie

    Thank for the codes.
    I want to copy the same worksheet to the workbook that I have opened.
    Is there any way to use the following code but without renaming the workbook where I want to copy the worksheet and maybe instead use something as 'ActiveWorkbook'

    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

  2. JE
    jerin

    ActiveSheet.moveorCopy = Worksheets(Sheets.Count)
    ihave error code 438

  3. JS
    Jitin Sahni

    Hi,

    If you can help me with converting different excel sheets in ONE workbook be transferred to Different Workbooks. For Example If I have a workbook containing different sheets Namely 1,2,3 upto 10 and want some tool or something to move them to 10 different workbooks namely 1,2,3, and so on. I have seen some one use this feature. Can you please help me.

More comments