Update excel VBA to choose range for renaming tabs in workbook

Question

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.

Please read Why is can someone hep me not an actual question — BigBen

chillin chillin · Accepted Answer · 2018-09-21T03:35:33

Seems like you're pretty much there already assuming your code works, unless I'm missing something.

Option Explicit

Sub RenameShts()
   Dim Ws As Worksheet
   Dim i As Long
   Dim Nme As String

    Dim columnLetter as string

    If Worksheets("Calculations").range("E1").value2 then columnLetter = "D" else columnLetter = "F"

   i = 2
   For Each Ws In Sheets(Array(Sheet6.Name, Sheet7.Name, Sheet8.Name, Sheet10.Name))
      Nme = Sheets("Calculations").Range(columnLetter & 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

Untested, written on mobile.

Update excel VBA to choose range for renaming tabs in workbook

1 Answers