Help referring to a sheet when I don't know the name of it

[mcomp72]

In my workbook, the names of some of the worksheets sometimes change. However, the sheet "numbers" never change.

I need to assign the name of a particular sheet to a variable, so I can refer to the name later on in code. I figured out how to do it, using this:

Dim NameOfSheet As String

NameOfSheet = Sheet28.Name

However, my understanding is I should always put ThisWorkbook in front of any sheet reference, in case another workbook is open at the time the code is running, because I don't want the variable to be assigned the name of Sheet28 on the other open sheet (assuming it has one).

The way I would have thought to do it is like this:

NameOfSheet = ThisWorkbook.Sheet28.Name

Unfortunately, this causes an error. Is there a way to refer to the actual sheet "number" (like Sheet28 above) and ALSO tell it that I am referring to ThisWorkbook?

 

[DanteAmor]

Maybe this helps a little

    Application.ScreenUpdating = False
    wb = ActiveWorkbook.Name
    ThisWorkbook.Activate
    NameOfSheet = Hoja28.Name
    Workbooks(wb).Activate
    Application.ScreenUpdating = True

 

[DanteAmor]

Another option

    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Sheets
        If sh.CodeName = "Hoja28" Then
            NameOfSheet = sh.Name
            Exit For
        End If
    Next

 

[MARK858]

However, my understanding is I should always put ThisWorkbook in front of any sheet reference

@mcomp72, sorry for asking this but who has told you this? the sheets codename only applies to ThisWorkbook (it is its one drawback), unless you use some convoluted code (see below for how to refer to a different workbook using its codename and you will see what I mean) and so it really isn't necessary to refer to ThisWorkbook when using it's codename.

some convoluted code

Workbooks("OtherWorkbook.xlsm").VBProject.vbcomponents("Sheet1").Properties("name").Value

 

[MARK858]

Too late to edit the previous post but to refer to a cell using it you would need....

Workbooks("OtherWorkbook.xlsm").Sheets(Workbooks("OtherWorkbook.xlsm").VBProject.vbcomponents("Sheet1").Properties("name").Value).Range("A1")

 

[Tetra201]

... Is there a way to refer to the actual sheet "number" (like Sheet28 above) and ALSO tell it that I am referring to ThisWorkbook?

The codename Sheet28 refers to the sheet in ThisWorkbook even if it is not the active workbook.

 

[mcomp72]

Thanks to all of you for your replies.

I did not know that if I used the codename Sheet28 it would automatically refer to ThisWorkbook. That is great to know.

In my coding, I generally refer to sheet names by their actual name, such as:

With ThisWorkbook.Sheets("Sheet1")

   ...some code...

End With

In this instance, is it necessary for me to put ThisWorkbook? Or is "Sheet1" automatically going to refer to ThisWorkbook?

 

[Tetra201]

From the Excel help: "Sheets Object: A collection of all the sheets in the specified or active workbook." So, if the parent is not explicitly specified, the Sheets("Sheet1") refers to the sheet in ActiveWorkbook.

 

[MARK858]

Just to be clear the sheets codename is unique in that it refers to the sheet the code resides in i.e. ThisWorkook not the ActiveWorkbook unlike the other ways of referencing the sheet i.e Index or Name.