Creating a loop to rename worksheets

[rbrace]

I hit a wall creating my first loop. The sheet rename was created for and works in ThisWorkbook but I want to convert it to loop through all worksheets as a button push.

Conditions:
Must exclude renaming 5 sheets.
B1 on each sheet contains an email or first name last name to be used as the sheet name in proper case.
If B1 is email address sheetname is name@domain
If B1 is text sheetname is first name and last initial
If error or duplicate sheetname is B1

Sub SetupWks() '(ByVal sh As Object, ByVal Target As Range)
Dim sh As Object
Dim Target As Range
 
 
 
' START LOOP ***********************************************************
   Dim ws  As Worksheet
   For Each sh In ActiveWorkbook.Worksheets
        Select Case sh.Name
        Case Is <> "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5"
 
        Case Else
            With sh
    
 
 
 
 
' START SHEET RENAME ***********************************************************
    If Target.Address <> "$B$1" Then Exit Sub
    If Trim(Target.Address) = "" Then Exit Sub
    On Error Resume Next
    
' IF Range is Email name sheet name@domain
    
    If Target Like "*[\!%^:~#|@.;`\/*$,""]*" Then
    sh.Name = Left(Target, Len(Target) - 4)
    sh.Name = StrConv(sh.Name, vbProperCase)
    Else
 
' If range is text name sheet first name last initial
    sh.Name = Split(Replace(Target, " ", " "), " ")(0) & " " & Left(Split(Replace(Target, " ", " "), " ")(1), 1)
    sh.Name = StrConv(sh.Name, vbProperCase)
    
' If error return B1
    If Err.Number <> 0 Then
    sh.Name = B1
    sh.Name = StrConv(sh.Name, vbProperCase)
 
      Exit Sub
    End If
    End If
    On Error GoTo 0
' END SHEET RENAME ***********************************************************
  
  
  
 
' END LOOP ***********************************************************
    End With
    End Select
    Next sh
 
End Sub

 

[Micron]

I guess your problem is that you can't use .Target because your procedure cannot make use of a parameter that doesn't exist? Would help if you stated what the problem is and if it's about a runtime error, provide the error number and message and id which line raises it.
That code layout sure isn't easy to read. Notwithstanding that AFAIK you cannot use Target like this, it should be properly indented and a lot of wasted space removed; more like

Sub SetupWks() '(ByVal sh As Object, ByVal Target As Range)
Dim sh As Object
Dim Target As Range
Dim ws As Worksheet

On Error Resume Next 
For Each sh In ActiveWorkbook.Worksheets
    Select Case sh.Name
        Case Is <> "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5"
 
        Case Else
            With sh
                If Target.Address <> "$B$1" Then Exit Sub
                If Trim(Target.Address) = "" Then Exit Sub
                'IF Range is Email name sheet name@domain
                If Target Like "*[\!%^:~#|@.;`\/*$,""]*" Then
                    sh.Name = Left(Target, Len(Target) - 4)
                    sh.Name = StrConv(sh.Name, vbProperCase)
                Else 'the comment that was here is inaccurate. Next section runs regardless of what it is
                    sh.Name = Split(Replace(Target, " ", " "), " ")(0) & " " & Left(Split(Replace(Target, " ", " "), " ")(1), 1)
                    sh.Name = StrConv(sh.Name, vbProperCase)
                    'If error return B1
                    If Err.Number <> 0 Then
                        sh.Name = B1
                        sh.Name = StrConv(sh.Name, vbProperCase)
                        Exit Sub
                    End If
                End If
                On Error GoTo 0  
            End With
    End Select
Next sh
 
End Sub

You might discover issues trying to use proper case with names like Paul O'Reilly. You'll get Paul O'reilly.

 

[rbrace]

I guess your problem is that you can't use .Target because your procedure cannot make use of a parameter that doesn't exist? Would help if you stated what the problem is and if it's about a runtime error, provide the error number and message and id which line raises it.
That code layout sure isn't easy to read. Notwithstanding that AFAIK you cannot use Target like this, it should be properly indented and a lot of wasted space removed; more like

Sub SetupWks() '(ByVal sh As Object, ByVal Target As Range)
Dim sh As Object
Dim Target As Range
Dim ws As Worksheet

On Error Resume Next
For Each sh In ActiveWorkbook.Worksheets
    Select Case sh.Name
        Case Is <> "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5"
 
        Case Else
            With sh
                If Target.Address <> "$B$1" Then Exit Sub
                If Trim(Target.Address) = "" Then Exit Sub
                'IF Range is Email name sheet name@domain
                If Target Like "*[\!%^:~#|@.;`\/*$,""]*" Then
                    sh.Name = Left(Target, Len(Target) - 4)
                    sh.Name = StrConv(sh.Name, vbProperCase)
                Else 'the comment that was here is inaccurate. Next section runs regardless of what it is
                    sh.Name = Split(Replace(Target, " ", " "), " ")(0) & " " & Left(Split(Replace(Target, " ", " "), " ")(1), 1)
                    sh.Name = StrConv(sh.Name, vbProperCase)
                    'If error return B1
                    If Err.Number <> 0 Then
                        sh.Name = B1
                        sh.Name = StrConv(sh.Name, vbThe code does not produce any errors but it  will not rename the worksheets and I do not see the screen updating as it loops so it may be exiting before the loop.
[/QUOTE]

 

[rbrace]

Thank you for the help. No error and screen does not update through the sheets with your changes.

 

[Micron]

It wasn't supposed to fix your problem since I kept what I said you could not do. It was to show you how to condense and indent your code and make it easier to read. If you need to use Target, then use an event that can make use of it. Otherwise, you would have to use Range or ActiveCell references. That can be tricky because ActiveCell won't be the one where the value was changed when someone tabs/Enters/clicks to another cell. I understand why you commented out the parameters portion of your first line, which is because it's not native to custom procedures (UDF's). AFAIK, you cannot use Target at all if it's not a parameter of the procedure.