Referring to a Worksheet Object

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