Looping a macro through all visible sheets?

[GlennJ]

I have a macro that does exactly what I need. I just need it to do the same thing to all sheets. Any help would be appreciated.

Sub Name_Sheet()

If Range("I14").Value = "" Then
    'Do nothing
Else
    ActiveSheet.Name = Range("I14").Value
End If

End Sub

 

[Fluff]

How about

Sub Name_Sheet()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      If Ws.Range("I14").Value <> "" Then Ws.Name = Ws.Range("I14").Value
   Next Ws
End Sub

 

[GlennJ]

How about

Sub Name_Sheet()
   Dim Ws As Worksheet
  
   For Each Ws In Worksheets
      If Ws.Range("I14").Value <> "" Then Ws.Name = Ws.Range("I14").Value
   Next Ws
End Sub

Thanks for getting back so quickly.

I think I need to explain better. The worksheet is already named Jane Doe, but if there is a Pickup By: Person, I need to change the sheet name to their name (Dave Smith). If there isn't a pickup person don't change the name.

 

[Rick Rothstein]

I think I need to explain better.

Why? What was wrong with the code Fluff posted?

 

[Fluff]

That image is not showing I14, so is not very helpful. In what way didn't the code work?

 

[GlennJ]

Hi Rick,

I get this error

 

[Fluff]

As you cannot have two sheets with the same name, what do you want to happen?

 

[Rick Rothstein]

The only way that would have happened is if the same name was put in cell I14 on more than one sheet. What is supposed to happen in that case?

 

[GlennJ]

If there's a pickup person, change the sheet name to the pickup person (Dave Smith). If there isn't a pickup person don't change the sheet name as it is already (Jane Doe). And repeat for all Sheets
Thanks for trying to work with me.

 

[Fluff]

Yes, but what should happen if that sheet name is already being used?