Here's three way to refer to a sheet
Method 1 and 3 does not change when you change the sheet name
Public Sub selectsheet()
Sheet1.Select
Sheets("Sheet1").Select
Sheets(1).Select
End Sub
A sheet in a workbook is normally referred to by specifying the Sheets collection and the name or the index number of the sheet. The following is the syntax of the this command:
Sheets("sheet name")
or Sheets(number)
For example, Sheets("Sheet1") specifies the sheet named Sheet1 in the active workbook. If Sheet1 were the first sheet in a file, then Sheets(1) would also refer to Sheet1 in the active workbook. If the sheets are moved or sheets added or deleted, the number that refers to a particular sheet can change.
To specify a sheet in a different workbook, the statement Workbooks("filename") can be used as the object qualifier. For example:
Workbooks("Personal.Xls").Sheets("Sheet1")
specifies sheet Sheet1 in Personal.Xls, no matter what workbook or sheet is currently active.
The Sheets collection consists of all types of sheets - worksheets, charts, dialogsheets, and Microsoft Excel 4 macro sheets. The collections Worksheets, Charts, Dialogs, Microsoft Excel4MacroSheets can also be used to specify a sheet:
Worksheets("Sheet1")
or Worksheets(1)
The Count property can be used to find out how many of a particular type sheet there are in a file:
MsgBox "There are " & Charts.Count & " chart sheets."
The following macro displays how many worksheets there are in the active file and then displays each one's name and index number.
Sub DisplayWorksheetNamesAndNumber()
Dim num As Integer, I As Integer
'Determine number of worksheets and display message
num = Worksheets.Count
MsgBox "There are " & num & _
" worksheets in this file"
'Display the number and name of each worksheet
For I = 1 To num
MsgBox "Worksheet " & I & " is called " & _
Worksheets(I).Name
Next I
End Sub
You can also use the keyword ActiveSheet to refer to the active sheet. For example if the active sheet is named "Survey Results", the following two statements both refer to this sheet:
Sheets("Survey Results")
or ActiveSheet