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

 

[GlennJ]

There isn't another sheet called Dave Smith. Every name is unique. I don't know what else to say.

Thanks for working with me.

I don't want to keep bugging you.
Have great weekend

 

[Fluff]

There isn't another sheet called Dave Smith. Every name is unique

Then you shouldn't be getting the error you showed in post#6. You will only get that error if you try to use the same sheet name twice.

 

[Fluff]

If you run this it will tell you what the current sheet name is & what I14 on that sheet contains, when you get an error.

Sub Name_Sheet()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      On Error Resume Next
      If Ws.Range("I14").Value <> "" Then Ws.Name = Ws.Range("I14").Value
      If Err.Number <> 0 Then MsgBox Ws.Name & vbLf & Ws.Range("I14").Value
      On Error GoTo 0
   Next Ws
End Sub

 

[GlennJ]

Somethings weird with my workbook.
I ran your error checker. It show the name of the first sheet and so on. As I click OK to each msg box, it then changes the names exactly as I want them, just not all at once.

I also tried making another fresh workbook to try your original Macro on and it works perfect there.

 

[Fluff]

Do you have any hidden sheets?

 

[GlennJ]

yes. There are 9 hidden sheets.
I also noticed that your error checker only give me a message box when here isn't a pickup person

 

[Fluff]

That suggests that I14 on those sheets is not actually empty.

 

[GlennJ]

They do have a formula in them =IFERROR(VLOOKUP(I10,'Combine all players'!B1:R1999,17,FALSE),"")
I also tried =VLOOKUP(I10,'Combine all players'!B1:R1999,17,FALSE)

On the sheets without a pickup person, I tried removing the formula and everything works perfect.

I didn't think the formula counted as something in the cell. I need the formula. Does this make sense to you?

 

[Fluff]

That formula is fine, are you sure it has "" at the end rather than " " (ie a space)

 

[GlennJ]

yes, that was a direct copy from the sheet =IFERROR(VLOOKUP(I10,'Combine all players'!B1:R1999,17,FALSE),"")

If I follow everything back to the raw data, this is the first formula used =IF(D4=0,"",D4). Could the zeros be coming through some how. The above formula traces back through 4 or 5 other formulas and one column is generated by another macro that is just a copy column and paste.