Hands On: Make your first macro

In any version of Excel you can always open the VBA editor, choose Module on the Insert menu and enter the listing for your macro in the right-hand panel.

A macro is either a subroutine which starts with Sub macro name () and finishes with EndSub or a custom function which begins with Function function name() and ends with End Function.

If a macro includes variables they will be of different types. By default they are called Variants but it is more efficient to classify or ‘dimension’ them at the start. An Integer variable only takes two bytes of memory.

A String variable only takes one byte per character. If you don’t dimension your variables then both types will become Variant variables which take 16 bytes plus one byte for each character, if the variable holds a string. That consumes a lot more memory and can reduce performance.

This particular macro needs two Integer variables for sorting the worksheets and one String variable for the names of the months. So, if you have deleted the notes, after Sub MonthTabs() enter:

Dim x As Integer
Dim y As Integer
Dim mth(12) As String

Now you can enter the names of the months which are to appear on the tabs. Type:

mth(1) = “Jan”
mth(2) = “Feb”
and so on down to:
mth(12) = “Dec”

This macro assumes that you are running it on a new workbook with worksheet tabs called Sheet1, Sheet2, Sheet3, though the number of sheets in the workbook doesn’t matter.

The next section of the macro uses a loop that says, if the first tab name starts with Sheets then change it to a month name, adding sheets if necessary. Enter:

For x = 1 To 12
If x <= Sheets.Count Then
If Left(Sheets(x).Name, 5) = “Sheet” Then
Sheets(x).Name = mth(x)
Else
Sheets.Add.Move after:=Sheets(Sheets.Count)
ActiveSheet.Name = mth(x)
End If
Else
Sheets.Add.Move after:=Sheets(Sheets.Count)
ActiveSheet.Name = mth(x)
End If
Next x

All the macro has to do then is move any other sheets to the end of the series, so enter:
For x = 1 To 12
If Sheets(x).Name <> mth(x) Then
For y = x + 1 To Sheets.Count
If Sheets(y).Name = mth(x) Then
Sheets(y).Move Before:=Sheets(x)
End If
Next y
End If
Next x