Microsoft Office Tutorials and References
In Depth Information
A Realistic Example That Uses Sub Procedures
4.
Sort the sheet names (somehow).
5.
Rearrange the sheets so they correspond to the sorted sheet names.
What you need to know
I saw a few holes in the plan. I knew that I had to determine the following:
h How to identify the active workbook
h How to count the sheets in the active workbook
h How to get a list of the sheet names
h How to sort the list
h How to rearrange the sheets according to the sorted list
When you lack critical information about specific methods or properties, you can
consult this topic or the VBA Help system. You may eventually discover what you need to
know. Your best bet, however, is to turn on the macro recorder and examine the code
that it generates when you perform some relevant actions. You’ll almost always get
some clues as to how to proceed.
Some preliminary recording
Here’s an example of using the macro recorder to learn about VBA. I started with a workbook
that contained three worksheets. Then I turned on the macro recorder and specified my Personal
Macro Workbook as the destination for the macro. With the macro recorder running, I dragged
the third worksheet to the first sheet position. Here’s the code that was generated by the macro
recorder:
Sub Macro1()
Sheets(“Sheet3”).Select
Sheets(“Sheet3”).Move Before:=Sheets(1)
End Sub
I searched the VBA Help for Move and discovered that it’s a method that moves a sheet to a new
location in the workbook. It also takes an argument that specifies the location for the sheet. This
information is very relevant to the task at hand. Curious, I then turned on the macro recorder to
see whether using the Move or Copy dialog box would generate different code. It didn’t.
 
Search JabSto ::




Custom Search