Referring to a Worksheet Object

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Because a Worksheet object exists as a member of a Worksheets collection, you refer to a worksheet by its name or its index value. In the following example, both object variables refer to the first worksheet in a workbook:

Sub ReferToWorksheetExample()
   ' This procedure illustrates how to programmatically refer to
   ' a worksheet.
   Dim wksSheetByIndex     As Excel.Worksheet
   Dim wksSheetByName      As Excel.Worksheet
   
   With ActiveWorkbook
      Set wksSheetByIndex = Worksheets(1)
      Set wksSheetByName = Worksheets("Main")
      If wksSheetByIndex.Index = wksSheetByName.Index Then
         MsgBox "The worksheet indexed as #" _
            & wksSheetByIndex.Index & vbCrLf _
            & "is the same as the worksheet named '" _
            & wksSheetByName.Name & "'", vbOKOnly, "Worksheets Match!"
      End If
   End With
End Sub

Note   You can also use the Application object's ActiveSheet property to return a reference to the currently active worksheet in the currently active workbook.

You can use the Microsoft® Visual Basic® for Applications (VBA) Array function to work with multiple worksheets at the same time, as shown in the following example:

Sub ReferToMultipleSheetsExample()
   ' This procedure shows how to programmatically refer to
   ' multiple worksheets.
   Dim wksCurrent As Excel.Worksheet
   
   With ActiveWorkbook.Worksheets(Array("Employees", "Sheet2", "Sheet3"))
      .FillAcrossSheets (Worksheets("Employees").UsedRange)
   End With
   Stop
   ' The worksheets named "Sheet2" and "Sheet3" should now
   ' contain the same table that is found on the "Employees"
   ' sheet. Press F5 to clear the contents from these worksheets.
   For Each wksCurrent In ActiveWorkbook _
      .Worksheets(Array("Sheet2", "Sheet3"))
      wksCurrent.UsedRange.Clear
   Next wksCurrent
End Sub

You can specify or determine the name of a worksheet by using its Name property. To change the name of a new worksheet, you first add it to the Worksheets collection and then set the Name property to the name you want to use.

See Also

Working with Microsoft Excel Objects | Understanding the Worksheet Object | Adding, Deleting, Copying, and Moving a Worksheet Object