VBA for assigning cell names based on code name

worksheetnames.vba

Sub GetValuesFromSheets()
    Dim ws As Worksheet
    Dim sheetNames As Variant
    Dim cellNames As Variant
    
    Dim i As Long
    Dim cellValue As Variant
    Dim cellName As Variant
    
    ' Define the code names of the sheets you want to loop over
    sheetNames = Array("AaaSheet", "EyySheet", "ByySheet")
    
    ' Cells in the corrisponding sheets that have the name
    cellNames = Array("B2", "A2", "B2")
    
    ' Loop over the sheets
    For i = LBound(sheetNames) To UBound(sheetNames)
        ' Get the sheet using its code name
        Dim wsCodeName As String
        wsCodeName = sheetNames(i)
        
        For Each ws In ThisWorkbook.Worksheets
            If ws.CodeName = wsCodeName Then
                ws.Select
                 
                ' Get the cellName from the same index we are using in sheetnames
                cellName = cellNames(i)
                'Get the value from cell
                cellValue = ws.Range(cellName).Value
        
                ' Do something with the value (e.g., print to Immediate Window)
                Debug.Print "Sheet " & sheetNames(i) & ": " & cellValue
                ws.Name = cellValue
            End If
        Next ws
    Next i
End Sub