Sheet Number Vs. Sheet Name

[MikeDBMan]

I wish to reference a sheet in my VBA code. What is the way to refer to a sheet by the name of the "tab name" instead of just the "Sheet 1", "Sheet 2", etc?

 

[Trevor G]

Sheets(1) rather than Sheets("Sheet1")

If you look in the VBA screen it will show you

Sheet1(Sheet1)

So Sheets(1) is the real name.

 

[VoG]

Or you can use the code name

Sheet1.Name="Data"

 

[MikeDBMan]

If I have this code:

Dim WSheet As Worksheet
    For Each WSheet In Worksheets
        If WSheet.Name <> "CustomName" Then                      
              Sheets(WSheet.Name).Select
        End If
    Next WSheet

I get a runtime error 1004 on the line "Sheets(WSheet.Name).select." The value of wsheet.name is "MyCustom" but the sheet is Sheet15. How can I convert from "MyCustom" to Sheet15? That is what I am asking. Or how to I select "MyCustom"?
Thanks.

 

[Jonmo1]

Is the sheet hidden?

Try

Dim WSheet As Worksheet
    For Each WSheet In Worksheets
        If WSheet.Name <> "CustomName" Then      
              WSheet.Visible = True
              WSheet.Select
        End If
    Next WSheet

 

[VoG]

One way

Dim WSheet As Worksheet
    For Each WSheet In Worksheets
        If WSheet.Index <> 15 Then
              WSheet.Select
        End If
    Next WSheet