Microsoft Office Tutorials and References
In Depth Information
Lesson Requirements
lesson requirements
For this lesson, you write a macro that uses a For…Next loop with an Integer type variable that adds
12 worksheets to your workbook, names each worksheet by calendar month (“January,” “February,”
and so on), and places the worksheets’ tabs in order of calendar month from left to right.
Open a new workbook and press Alt+F11 to go to the Visual Basic Editor.
From the menu at the top of the VBE, click Insert Module.
In the module you just created, type Sub LoopTwelveMonths and press Enter. VBA will
automatically place a pair of empty parentheses at the end of the Sub line, followed by an
empty line, and the End Sub line below that. Your macro will look like this so far:
Sub LoopTwelveMonths ()
End Sub
Declare an Integer type variable that will iterate 12 times, one for each month of
the year:
Dim intMonth As Integer
Open a For...Next loop that starts from 1 and ends at 12:
For intMonth = 1 To 12
With a one-line command, you can add each of the 12 worksheets in turn, while placing
their tabs one after another from left to right, and naming each tab by calendar month. The
DateSerial function is a good way to cycle through month names because it requires integer
values for the arguments of Year, Month, and Day, just like the DATE worksheet function.
You can use any year, and any day that is not a number greater than 28. For the Month
argument, the intMonth variable is a perfect fit because it was declared as an Integer type.
Sheets.Add(After:=Sheets(Sheets.Count)).Name = _
Format(DateSerial(2011, intMonth, 1), “MMMM”)
Enter the Next statement for the intMonth variable that will produce and name the next
month’s worksheet up to and including December:
Next intMonth
When completed, the macro will look like this, with comments that have been added to
explain each step:
Sub LoopTwelveMonths()
‘Declare an Integer type variable to iterate twelve times,
‘one for each month of the year.
Dim intMonth As Integer
Search JabSto ::

Custom Search