Select a worksheet using VBA in Excel

Select worksheet by name

The Item property of the Worksheets collection is used to returns a single Worksheet object from the collection.

As an example, it is called like this:

Dim ws As Worksheet
Set ws = Application.ActiveWorkbook.Worksheets.Item(Index:="data")

The  Worksheets collection here is obtained froma  Workbook.Worksheets procedure call. The Item procedure returns an object of type  Worksheet  which is optionally assigned to a variable ws .

... or shorter:

Dim ws As Worksheet
Set ws = Worksheets(Index:="data")

Dim strWorkbook As String: strWorkbook = "Demo.xlsx"
Dim wb As Workbook
Set wb = Workbooks(strWorkbook)
Dim wsData As Worksheet
Set wsData = wb.Worksheets("Data")

Select sheet by index number

A worksheet can also be identified by its number in the collection, where sheet 1 corresponds to the first worksheet.

Dim ws As Worksheet
Set ws = Worksheets(1)

Select worksheet by variable name

Except with general purpose macros, you will often know what the name is of the worksheets your macro acts on.

Dim strWorkbook As String: strWorkbook = "Demo.xlsx"
Dim wb As Workbook
Set wb = Workbooks(strWorkbook)
Dim strWorksheet As String: strWorksheet = "data" 
Dim ws As Worksheet
Set ws = wb.Worksheets(strWorksheet)

Still, the sheetname may include a variable element, e.g. the date (formatted) of some production report as csv file. In such a case you may loop through the Worksheets as below where the Like "...*" wildcard will return the required worksheet with the name starting with "Data" and some unspecified end:

Dim wsData As Worksheet
Dim ws As Worksheet
For Each ws In wb.Worksheets
	If ws.Name Like "Data*" Then
		Set wsData = ws
		Exit For
	End If
Next ws

Select worksheet by codename

If your macro only needs to work on sheets of the workbook in which the macro is located you can use a shorter syntax:

Sheet1.Range("A1").Value = "Hello world"

Select sheet to the left

Use the Previous property to get the sheet to the left. Use the Next property to get the sheet to the right.

Dim wsPrevious As Worksheet
Set wsPrevious = Sheet2.Previous
If Not wsPrevious Is Nothing Then
    
End If

Select sheet to the right

Use the Next property to get the sheet to the right.

Dim wsNext As Worksheet
Set wsNext = Sheet2.Next

Select worksheet range

Ultimately, you will probably want to do things with a range in the worksheet for which you will use a Range variable.

Dim strWorkbook As String: strWorkbook = "Demo.xlsm"
Dim wb As Workbook
Set wb = Workbooks(strWorkbook)
Dim strWorksheet As String: strWorksheet = "data"
Dim ws As Worksheet
Set ws = wb.Sheets(strSheet)
Dim rng As Range
Set rng = ws.Range("A2:B6")

Runtime error 9: subscript out of range

If the Index refers to an item not present in the collection this will returns Runtime error 9: subscript out of range. Depending on the actual use, you may consider adding error handling.

Sub Demo()
	On Error GoTo HandleError
	Set ws = Worksheets("xxx")
	
HandleExit:
	Exit Sub
HandleError:
	MsgBox Err.Description
	Resume HandleExit
End Sub

Open worksheet event

You can use the Worksheet_Activate() procedure which runs when the sheet is selected. To add the code open the worksheet module (select in the Project explorer) and in the left module combo select Worksheet and in the right combo select Activate. This will insert an empty code block. then complete it to:

Private Sub Worksheet_Activate()
	MsgBox "Hi! I'm " & Me.Name & ", who are you?"
End Sub