Refering to codename of worksheet

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,825
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
IF I know my workbook has a worksheet called Apples, I can refer to it as follows:

Code:
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:


Code:
With wb.Worksheets(wksApples.Codename)


    ' Do something


End With

I don't want to have to do this:


Code:
    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
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
You will have to use the loop if you want the workbook reference i believe. You cant use wb.wksApples for example.
 
Upvote 0
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
 
Upvote 0
You don't need to loop to find a sheet with a given code name


Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top