VBA Move Worksheet Explained with Examples

Home/VBA/Move Worksheet

Move worksheet in VBA is used to move the worksheet(s) from one location to another location in the same workbook or another new workbook or existing workbook. Here Move is method of worksheet object. Sometimes we may want to move worksheet in the active workbook at the beginning of the worksheet or in between worksheets or at the end of worksheet. Need basis we can move the worksheets using Move worksheet method in VBA.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


In this Topic:

VBA Move Worksheet: Syntax

Please find the below syntax to Move Worksheet at the beginning of the Worksheet or In-between Worksheet’s or end of the Worksheet using VBA.

Worksheets(“Worksheet Name”).Move([Before], [After])

Where
Before: It’s an Optional parameter. The worksheet will be moved to before the specified worksheet. Then we can’t specify after parameter.
After: It’s an Optional parameter. The worksheet will be moved to after the specified worksheet. Then we can’t specify after parameter.

Note: If we don’t specify Optional parameter either Before or After, Excel will create new workbook and it contains moved Worksheet.

VBA Move Worksheet: Using Before

Please find the below, It will show you how to Move the Worksheet to the beginning.

Sub MoveSheet_Beginning()
Worksheets("Sheet3").Move Before:=Worksheets(1)
End Sub

In the above we are moving the Worksheet named ‘Sheet3’ to the beginning of the worksheet. Where ‘1’ represents the Worksheet index number (Nothing but first available sheet in the workbook).

Sub MoveSheet_Beginning1()
ActiveSheet.Move Before:=Worksheets(1)
End Sub

In the above we are moving the active worksheet to the beginning of the worksheet.

VBA Move Worksheet: Using After

Please find the below, It will show you how to Move the Worksheet at the end of the available worksheets.

Sub MoveSheet_End()
Worksheets("Sheet3").Move After:=Worksheets(Worksheets.Count)
End Sub

In the above we are moving the Worksheet named ‘Sheet3’ to the end of the worksheet. Where ‘Worksheets.Count’ represents the number of available worksheets in the workbook

Sub MoveSheet_End()
ActiveSheet.Move After:=Worksheets(Worksheets.Count)
End Sub

In the above we are moving the active worksheet to the end of the worksheet.

VBA Move Worksheet: Before Specified Worksheet

Please find the below example, It will show you how to Move the Worksheet either before or after the specified worksheet.

Sub MoveSheet_Before()
Worksheets("Sheet2").Move Before:=Sheets("Sheet5")
End Sub

Please find the above, we are moving ‘Sheet2’ to the before ‘Sheet5’.

Sub MoveSheet_Before()
ActiveSheet.Move Before:=Sheets("Sheet5")
End Sub

In the above, we are moving active sheet to the before ‘Sheet5’.

Sub MoveSheet_After()
Worksheets("Sheet2").Move After:=Sheets("Sheet5")
End Sub

Please find the above, we are moving ‘Sheet2’ to the after ‘Sheet5’.

Sub MoveSheet_After()
ActiveSheet.Move After:=Sheets("Sheet5")
End Sub

In the above, we are moving active sheet to the after ‘Sheet5’.

VBA Move Worksheet: To New Workbook

Please find the below, It will show you how to Move the Worksheet named ‘Sheet1’ to new workbook.

Sub MoveSheet_NewWorkbook()
Sheets("Sheet1").Move
End Sub

Please find the below, It will move the active worksheet to new workbook.

Sub MoveSheet_NewWorkbook()
ActiveSheet.Move
End Sub

VBA Move Worksheet: To Specific Workbook

Please find the below, It will show you how to Move the Worksheet named ‘Sheet1’ to Specific workbook before ‘Sheet3’.

Sub MoveSheet_SpecificWorkbook ()
Sheets("Sheet1").Move Before:=Workbooks("YourWorkbookName.xls").Sheets(“Sheet3”)
End Sub

Please find the below, It will move the active worksheet to Specific workbook after ‘Sheet3’.

Sub MoveSheet_SpecificWorkbook ()
ActiveSheet.Move After:=Workbooks("YourWorkbookName.xls"). Sheets(“Sheet3”)
End Sub

VBA Move Worksheet: Instructions

Please follow the below step by step instructions to execute the above mentioned VBA macros or codes:
Step 1: Open an Excel Worksheet
Step 2: Press Alt+F11 to Open VBA Editor
Step 3: Insert a Module from Insert Menu
Step 4: Copy the above code for activating a range and Paste in the code window (VBA Editor)
Step 5: Save the file as macro enabled Worksheet
Step 6: Press ‘F5′ to run it or Keep Pressing ‘F8′ to debug the code line by line and have a look how the Worksheet(s) moving in the workbook.

LIMITED TIME OFFER
By |March 8th, 2015|VBA|0 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

Leave A Comment


Related pages


how to find and delete duplicates in excelfind duplicates in excel listexcel unprotectshort key to insert row in excelcombobox itemsms excel vlookupdelete duplicate row in excelhow to create dashboard in excel 2007excel insert row with formulafor loop in excel macrovlookup table in excelvba protect all sheetsvba select a worksheetwhy does vlookup return 0minutes proformavba yes no boxuserform in excelexcel arithmetic operatorsrange.find vbamerge 2 columns in excelvba pdf to excelauto fit excelvba excel copy rangeexcel deduplicationsheet tab excelcreate pivot table using macroexcel hlookuphow to append excel filesprotect formulas in excel 2007what is the password to unprotect a sheet on excelautofit row heightlistbox userform vbasql coding for beginnersexcel protect worksheetsumifs excel 2007how to use vlookup 2010autosave excel 2007project management spreadsheet excel templatesort smallest to largest excelsql aptitude questions with answersvba sort columnvb scripting interview questions and answersclose file vbaconstruction schedule template excelmysql excel connectorexcel advanced filter tutorialhow to create a column chart in excel 2013delete cells in excelexcel macro file formatcopy a worksheet to another workbookvba selection.deletebypass excel passwordconditional in excelactivex examplemsgbox yes no vbaconsolidate in excelexcel 2010 powerpivot tutorialmacro writing tutorialdaily task tracker on excel formatgetting rid of duplicates in excelhiding a column in excelhow do i insert rows in exceladd developer tab to excel 2007fso filehow to insert combobox in excelexcel tutorial vlookupmacro in excel 2010 with exampleunmergeduplicate cells in excelvba directoryexcel protect formulas without protecting worksheetisna excel functionclose file vbafind duplicates excelshort cut excelremove all blank rows excel