Rename 4 tabs only based on cell value with VBA

[Stuepef]

I have a workbook that has 14 total tabs and I am looking to write VBA to rename only 4 of the 14 tabs. The tabs to be renamed will use cell values on a different tab that contain no illegal characters or length restrictions. I have researched how to write the code, but only found the cell values are in the same spot on each workbook which is not the situation here. If the cell value is blank or 0, I want to rename the tab to "HIDE".

worksheets to be renamed:
Summary 1
Summary (2)
Summary (3)
Summary (4)

worksheet with cell values for renaming:
Overall Summary
cell A24 for Summary 1
cell A25 for Summary (2)
cell A26 for Summary (3)
cell A27 for Summary (4)

Thank you for the help!

 

[Fluff]

Is this a one-off event or will you need to rename those tabs again?
Also what if more then 2 of the cells are blank?

 

[Stuepef]

good questions, the renaming of tabs will be a one time event and I didn't think about if more than 2 cells are blank. The reasoning behind the HIDE part is I was going to write another VBA to hide tabs that are labeled HIDE, but I could just incorporate that into 1 VBA.

 

[Fluff]

How about

Sub RenameShts()
   Dim Ws As Worksheet
   Dim i As Long
   Dim Nme As String
   
   i = 24
   For Each Ws In Sheets(Array("Summary 1", "Summary (2)", "Summary (3)", "Summary (4)"))
      Nme = Sheets("Overall Summary").Range("A" & i).Value
      If Nme = "" Or Nme = 0 Then
         Ws.Visible = xlSheetHidden
      Else
         Ws.Name = Nme
      End If
      i = i + 1
   Next Ws
End Sub

 

[Stuepef]

I receive the following error:
Run-Time error '9':
subscript out of range

 

[Fluff]

Check that all the sheet names are correct, including any leading/trailing spaces

 

[Stuepef]

I double checked all names for leading/trailing spaces and I still receive the same error message.

 

[Fluff]

What line gives the error?

 

[Stuepef]

It doesn't show me which specific line, but when I scroll through the code with F8, it errors out at this point

If Nme = "" Or Nme = 0 Then
         Ws.Visible = xlSheetHidden

with the top line highlighter.

 

[Fluff]

Ok, try

      If Nme = "" Or Nme = "0" Then