Refering to codename of worksheet

[tiredofit]

IF I know my workbook has a worksheet called Apples, I can refer to it as follows:

With wb.Worksheets("Apples")

    ' Do something

End With

but I DON'T know for certain there's a worksheet called Apples because the user might change it.


Instead I DO know there's a worksheet with a codename of wksApples.


So how can incorporate this into my code?


This doesn't work:

With wb.Worksheets(wksApples.Codename)


    ' Do something


End With

I don't want to have to do this:

    Dim ws As Worksheet


    For Each ws In wb.Worksheets


        If ws.CodeName = "wksApples" Then


            Exit For


        End If


    Next ws


    With ws


        ' Do something


    End With

Thanks

 

[Tim_Excel_]

With wksApples

'your code here

End With

 

[steve the fish]

You will have to use the loop if you want the workbook reference i believe. You cant use wb.wksApples for example.

 

[tiredofit]

Thanks

 

[Fluff]

If the sheet is in the same workbook as the code then you can use the code the Tim_Excel_ supplied.
Otherwise you will need to use the loop

 

[mikerickson]

You don't need to loop to find a sheet with a given code name

Sub test()
    MsgBox WorksheetCodeNamed("wksApples").Range("A1").Value
End Sub

Function WorksheetCodeNamed(wsCodeName As String, Optional wb As Workbook)
    If wb Is Nothing Then Set wb = ActiveWorkbook
    
    With wb
        Set WorksheetCodeNamed = .Sheets(.VBProject.VBComponents(wsCodeName).Properties("index").Value)
    End With
End Function

 

[Tim_Excel_]

If the sheet is in the same workbook as the code then you can use the code the Tim_Excel_ supplied.
Otherwise you will need to use the loop

Speaking of which, any idea why this is the case? It would make some of our lives a whole lot easier if we could refer to the codename of a worksheet in a specific WB.

 

[mikerickson]

Speaking of which, any idea why this is the case? It would make some of our lives a whole lot easier if we could refer to the codename of a worksheet in a specific WB.

You don't need to loop.

 

[Tim_Excel_]

You don't need to loop.

Either loop or use a function, but you can't directly refer to Wb.Ws when you're referring to a codename. I wonder why that is.

 

[steve the fish]

The loop would be so fast as not to notice anyhow.