Update excel VBA to rename tabs based on cell value

Stuepef

Board Regular
Joined
Oct 23, 2017
Messages
110
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.

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

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,287
Office Version
365
Platform
Windows
Try this

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,287
Office Version
365
Platform
Windows
thanks for the feedback (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,101,802
Messages
5,482,982
Members
407,371
Latest member
gdjenkins80

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top