Macro 20: Moving Worksheets Around
We’ve all had to rearrange our spreadsheet so that some sheet came before or after other sheet. If
you find that you often have to do this, here is a macro that can help.
How it works
When you want to rearrange sheets, you use the Move method of either the Sheets object or the
ActiveSheet object. When using the Move method, you need to specify where to move the sheet
to. You can do this using the After argument, the Before argument, or both.
Sub Macro20()
‘Move the active sheet to the end
ActiveSheet.Move After:=Worksheets(Worksheets.Count)
‘Move the active sheet to the beginning
ActiveSheet.Move Before:=Worksheets(1)
‘Move Sheet 1 before Sheet 12
Sheets(“Sheet1”).Move Before:=Sheets(“Sheet12”)
End Sub
This macro does three things. First, it moves the active sheet to the end. 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. This means that we can enter something like Worksheets(3)
to point to the third sheet in a workbook. Thus, you can use Worksheet(Worksheets.Count) to
point to the last sheet.
Next, this macro moves the active sheet to the beginning of the workbook. This one is simple; we use
Worksheets(1) to point to the first sheet in the workbook, and then move the active sheet before
that one.
Finally, the macro demonstrates that you can move sheets simply by calling them out by name. In
this example, we are moving Sheet1 before Sheet12.
How to use it
The best place to store this kind of a macro is in your Personal Macro Workbook. This way, the macro
is always available to you. The Personal Macro Workbook is loaded whenever you start Excel. In the
VBE Project window, it is named personal.xlsb.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click personal.xlb in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code in the newly created module.
If you don’t see personal.xlb in your project window, it means it doesn’t exist yet. You’ll have to
record a macro, using Personal Macro Workbook as the destination.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option
in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-
down list. Simply record a couple of cell clicks and then stop recording. You can discard the recorded
macro and replace it with this one.
