Moving a Worksheet in Excel VBA

The following VBA procedure moves an existing worksheet to a new position within the same workbook:

Sub MoveSheet()
    ThisWorkbook.Activate
    Worksheets("Sheet2copy").Move Before:=Worksheets("Sheet2")
End Sub

Explanation:

• Activate the current workbook
• Activate

This ensures that the macro applies to the workbook where the VBA code resides, which is important when more than one workbook is open.

Move the worksheet

Worksheets(« Sheet2copy »).Move Before:=Worksheets(« Sheet2 »)

• • The Move method is used to relocate the worksheet named « Sheet2copy ».
  • It is inserted before the worksheet named « Sheet2 ».
  • After moving, the sheet becomes the active worksheet.

Working of the Move Method:

• Just like the Copy method, Move supports named parameters:
  • Before:=… inserts the sheet before the specified worksheet.
  • After:=… inserts it after a specific sheet.
  • If no parameter is provided, the sheet is moved to a new workbook.

Comparison with the Copy Method:

Key Takeaway:

The Move method offers fine control over the positioning of a worksheet within the workbook or across workbooks. Using named parameters like Before and After not only improves clarity but also prevents unexpected behavior.