MS-Excel / General Formatting

---

Dealing with Worksheet Objects

Worksheet objects contain a number of properties and methods you can exploit in your code. These include options for activating and hiding worksheets, adding new worksheets to a workbook, and moving, copying, and deleting worksheets. The next few sections discuss these and other worksheet operations.

Specifying a Worksheet Object

If you need to deal with a worksheet in some way, or if your code needs to specify an object contained in a specific worksheet (such as a range of cells), you need to tell Excel which worksheet you want to use. To do this, use the Worksheets object. Worksheets is the collection of all the worksheets in a particular workbook. To specify a worksheet, either use its index number (where 1 represents the first worksheet tab, 2 the second worksheet tab, and so on) or enclose the worksheet name in quotation marks. For example, if Sheet1 is the first worksheet, the following two statements would be equivalent:

Worksheets(1)
Worksheets("Sheet1")

Alternatively, if you want to work with whichever worksheet is currently active in a specified Workbook object, use the ActiveSheet property, as in this example:

currentWorksheet = Workbooks("Budget.xlsx").ActiveSheet

If you need to work with multiple worksheets (say, to set up a 3D range), use VBA's Array function with the Worksheets collection. For example, the following statement specifies the Sheet1 and Sheet2 worksheets:

Worksheets(Array("Sheet1","Sheet2"))

Creating a New Worksheet

The Worksheets collection has an Add method you can use to insert new sheets into the workbook. Here's the syntax for this method:

Worksheets.Add([Before][, After][, Count][, Type])

Before
The sheet before which the new sheet is added. If you omit both Before and After, the new worksheet is added before the active sheet.

After
The sheet after which the new sheet is added. Note that you can't specify both the Before and After arguments.

Count
The number of new worksheets to add. VBA adds one worksheet if you omit Count. If you set Count greater than 1, all the sheets are added in the same location, as specified by either Before or After.

Type
The type of worksheet. You have three choices-xlWorksheet (the default) and two constants that create Excel 4 macro sheets (which, therefore, you'll never use, because Excel 4 macros are long obsolete): xlExcel4MacroSheet and xlExcel4IntlMacroSheet.

In the following statement, a new worksheet is added to the active workbook before the Sales sheet:

Worksheets.Add Before:=Worksheets("Sales")

Properties of the Worksheet Object

Let's take a tour through some of the most useful properties associated with Worksheet objects:

• Worksheet.Name-Returns or sets the name of the specified Worksheet. For example, the following statement renames the Sheet1 worksheet to 2007 Budget:
  Worksheets("Sheet1").Name = "2007 Budget"
• Worksheet.StandardHeight-Returns the standard height of all the rows in the specified Worksheet.
• Worksheet.StandardWidth-Returns the standard width of all the columns in the specified Worksheet.
• UsedRange-Returns a Range object that represents the used range in the specified Worksheet.
• Worksheet.Visible-Controls whether or not the user can see the specified Worksheet. Setting this property to False is equivalent to selecting Format, Sheet, Hide. For example, to hide a worksheet named Expenses, you would use the following statement:
  Worksheets("Expenses").Visible = False
  To unhide the sheet, set its Visible property to True.

Methods of the Worksheet Object

Here's a list of some common Worksheet object methods:

• Worksheet.Activate-Makes the specified Worksheet active (so that it becomes the ActiveSheet property of the workbook). For example, the following statement activates the Sales worksheet in the Finance.xlsx workbook:
  Workbooks("Finance.xlsx").Worksheets("Sales").Activate
• Worksheet.Calculate-Calculates the specified Worksheet. For example, the following statement recalculates the Budget 2007 worksheet:
  Worksheets("Budget 2007").Calculate
• Worksheet.Copy-Copies the specified Worksheet to another location in the same workbook using the following syntax:
  Worksheet.Copy([Before][, After])

  Worksheet
  The worksheet you want to copy.
  
  Before
  The sheet before which the sheet will be copied. If you omit both Before and After, VBA creates a new workbook for the copied sheet.
  
  After
  The sheet after which the new sheet is added. You can't specify both the Before and After arguments.

  In the following statement, the Budget 2007 worksheet is copied to a new workbook:
  Worksheets("Budget 2007").Copy
• Worksheet.Delete-Deletes the specified Worksheet. For example, the following statement deletes the active worksheet:
  ActiveSheet.Delete
• Worksheet.Move-Moves the specified Worksheet to another location in the same workbook using the following syntax:
  Worksheet.Move([Before][, After])

  Worksheet
  The worksheet you want to move.
  
  Before
  The sheet before which the sheet will be moved. If you omit both Before and After, VBA creates a new workbook for the moved sheet.
  
  After
  The sheet after which the new sheet is added. You can't specify both the Before and After arguments.

  In the following statement, the Budget 2007 worksheet is moved before the Budget 2006 worksheet:
  Worksheets("Budget 2007").Move Before:=Worksheets("Budget 2006")
• Worksheet.Select-Selects the specified Worksheet.