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").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 worksheets 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 . | | Type | The type of worksheet. You have three choices ” xlWorksheet (the default) and two constants that create Excel 4 sheets (which, therefore, you'll never use): 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 2004 Budget: Worksheets("Sheet1").Name = "2004 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.xls workbook: Workbooks("Finance.xls").Worksheets("Sales").Activate Worksheet .Calculate ” Calculates the specified Worksheet . For example, the following statement recalculates the Budget 2004 worksheet: Worksheets("Budget 2004").Calculate Worksheet .Copy ” Copies the specified Worksheet 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 2004 worksheet is copied to a new workbook: Worksheets("Budget 2004").Copy Worksheet .Delete ” Deletes the specified Worksheet . For example, the following statement deletes the active worksheet: ActiveSheet.Delete Worksheet .Move ” Moves the specified Worksheet 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 2004 worksheet is moved before the Budget 2003 worksheet: Worksheets("Budget 2004").Move Before:=Worksheets("Budget 2003") Worksheet .Select ” Selects the specified Worksheet . | 