Activate sheets by codename from the Personal.xlsb

[Rowland Hamilton]

How do I activate/access sheets by codename from the Personal.xlsb
I want it to work for 2 scenarios:
1) Recently downloaded Excel report in the temporary folder, with one sheet, Worksheet name and codename are both sheet1.

2) Workbook has been saved and the worksheet name has been changed from Sheet1 but the codename is still Sheet1 and I want to refer to the codename.

 

[shg]

You can only refer to a sheet by Codename in the workbook in which the sheet exists.

You can set an object variable to refer to a particular sheet in any workbook, and changing the sheet name won't affect the object variable; it will still refer to the same sheet.

 

[Rowland Hamilton]

I tried this but it doesn't actually transfer the codename into my macro:

Public Function GetWorksheetFromCodeName( _
      ByVal CodeName As String) As Worksheet

' Return the worksheet with the requested code name.
   Dim FocusSheet As Object
   
   For Each FocusSheet In ThisWorkbook.Worksheets
      If FocusSheet.CodeName = CodeName Then
         Set GetWorksheetFromCodeName = FocusSheet
         Exit Function
      End If
   Next FocusSheet

End Function

Then I tried the macro:

Sub Test_Won()
Dim wsPivot As Excel.Worksheet
Set wsPivot = GetWorksheetFromCodeName(sht3NURPLE)
MsgBox wsPivot.Name
End Sub

Note: I codnamed Sheet3 as sht3NURPLE and renamed the sheetname as "THIRD" for testing.

 

[shg]

sht3NURPLE should be in quotes.

 

[Rowland Hamilton]

Thanks. I actually typed the wrong thing, it was in quotes.
her is the exact copy/paste:

Public Function GetWorksheetFromCodeName( _
      ByVal CodeName As String) As Worksheet

' Return the worksheet with the requested code name.
   Dim FocusSheet As Object
   
   For Each FocusSheet In ThisWorkbook.Worksheets
      If FocusSheet.CodeName = CodeName Then
         Set GetWorksheetFromCodeName = FocusSheet
         Exit Function
      End If
   Next FocusSheet

End Function
Sub Test_Won()
Dim wsPivot As Excel.Worksheet
Set wsPivot = GetWorksheetFromCodeName("sht3NURPLE")
MsgBox wsPivot.CodeName
End Sub

 

[Rowland Hamilton]

Doesn't work with

MsgBox wsPivot.Name

either, nothing captures for wsPivot.

After patiently stepping through the loop of the Function, I realized it did pass the codename to the function, but it is looping through the personal.xlsb. Since it was only selecting "Sheet1" and not looping to a next sheet, I changed all 3 sheetnames and codenames in the test xlsm workbook (keeping "sht3NURPLE"). WHen I still selected Sheet1, I knew it was the personal xlsb (could have put watch on workbook's name as well, I guess).

Need to activate the workbook and not the Personal.xlsb

 

[Rowland Hamilton]

Had to change

ThisWorkbook.Worksheets

to

ActiveWorkbook.Worksheets

Public Function GetWorksheetFromCodeName( _
      ByVal CodeName As String) As Worksheet

' Return the worksheet with the requested code name.
   Dim FocusSheet As Object
   
   For Each FocusSheet In ActiveWorkbook.Worksheets
      If FocusSheet.CodeName = CodeName Then
         Set GetWorksheetFromCodeName = FocusSheet
         Exit Function
      End If
   Next FocusSheet

End Function
Sub Test_Won()
Dim wsPivot As Excel.Worksheet
Set wsPivot = GetWorksheetFromCodeName("sht3NURPLE")
MsgBox wsPivot.Name
End Sub

 

[Rowland Hamilton]

Now it works. Thanks.

 

[Rowland Hamilton]

Sub Test_Won()
Dim wsPivot As Excel.Worksheet
Set wsPivot = GetWorksheetFromCodeName("sht3NURPLE")
MsgBox wsPivot.CodeName

More like what I want (will apply this method to another macro that relies on codenames).

 

[Rowland Hamilton]

How do I fix this: Code now failing. For some reason the code only works when I run it from the VBE module the first time? If I open the regular Excel workbook with the data I want to manipulate and click the macro quick button, it doesn' twork, but when I go to the code in the personal.xlsb and then click run, it works. After that, I can click the quick button on my quick menu and it does work, just needs to initially launch from the VBE window. HUH?

Sub Parse_Task_Number_and_Report_Cleanup()
Dim wbHome As Workbook
Dim wsD As Worksheet 'Source worksheet - "Download"
Dim wsR As Worksheet 'Results worksheet
Dim lngFRow As Long 'First row
Dim lngLRow As Long 'Last row
Dim lngLCol As Long 'Last column
Dim MyRow As Long 'First row through input box
Dim rngTable As Range 'Table data range
Dim rngDownload As Range 'download area to be copied
Dim MyTestRange As Range
Dim oChart_pic As Object
'Set Headers for new parsed columns
    Dim headerArray()
    Dim Destination As Range

Application.ScreenUpdating = False
Application.EnableEvents = True
Set wbHome = ActiveWorkbook
'Name and Identify the download sheet to which you first add new data
ThisWorkbook.Activate
wbHome.Activate
Set wsD = GetWorksheetFromCodeName("Sheet1")
wsD.Name = "Download"

Here is function:

Public Function GetWorksheetFromCodeName(ByVal CodeName As String) As Worksheet
' Return the worksheet with the requested code name.
   Dim FocusSheet As Object
   
   For Each FocusSheet In ActiveWorkbook.Worksheets
      If FocusSheet.CodeName = CodeName Then
         Set GetWorksheetFromCodeName = FocusSheet
         Exit Function
      End If
   Next FocusSheet
End Function