Update excel VBA to rename tabs based on cell value

[Stuepef]

Current: I have code that updates 4 tab (Sheet6, Sheet7, Sheet8, Sheet10) names based on cell values in range D2:D5. This range is populated with formulas, which can result in blanks and is located on a tab named “Calculations”. Each cell is associated with a specific tab:

D2 -> Sheet6. If cell D2 is blank or 0, then hide tab. If not blank, name Sheet6 with text in cell D2.
D3 -> Sheet7. If cell D3 is blank or 0, then hide tab. If not blank, name Sheet7 with text in cell D3.
D4 -> Sheet8. If cell D4 is blank or 0, then hide tab. If not blank, name Sheet8 with text in cell D4.
D5 -> Sheet10. If cell D5 is blank or 0, then hide tab. If not blank, name Sheet10 with text in cell D5.

Sub RenameShts()  
   Dim Ws As Worksheet
   Dim i As Long
   Dim Nme As String
   
   i = 2
   For Each Ws In Sheets(Array(Sheet6.Name, Sheet7.Name, Sheet8.Name, Sheet10.Name))
      Nme = Sheets("Calculations").Range("D" & i).Value
      If Nme = "" Or Nme = "0" Or Nme = "0 0" Then
         Ws.Visible = xlSheetHidden
      Else
         Ws.Name = Nme
      End If
      i = i + 1
   Next Ws
End Sub

Desired code: Update current code to look at cell E1 on the “Calculations” tab to determine which range to use for naming the 4 tabs (Sheet6, Sheet7, Sheet8, Sheet10). If cell E1 = False, use range D2:D5 for naming the tabs. If cell E1 = True, use range F2:F5 for naming the tabs. I want the same logic to apply, if any of the cells in the range being used is blank or 0, then hide the tab completely.

 

[Yongle]

Try this

Sub RenameShts()
    Dim ws As Worksheet, i As Long, nme As String, col As String
    
    If Sheets("Calculations").Range("E1") = False Then col = "D" Else col = "F"
    i = 2
    For Each ws In Sheets(Array(Sheet6.Name, Sheet7.Name, Sheet8.Name, Sheet10.Name))
        nme = Sheets("Calculations").Range(col & i).Value
        If nme = "" Or nme = "0" Or nme = "0 0" Then
            ws.Visible = xlSheetHidden
        Else
            ws.Visible = True
            ws.Name = nme
        End If
        i = i + 1
   Next ws
End Sub

 

[Stuepef]

@Yongle - this is great and works perfectly. Thank You!

 

[Yongle]

thanks for the feedback