Rename Sheets in a Workbook

Hi Everyone,
the following code gives all worksheets in a workbook the name of the string found in cell A1.

But I have the problem that I have sheets which have only one word. Then the script does not work anymore.

Also I would like to have the complete content of cell A1 as sheet name no matter what is in it.

Maybe you can help me to adjust the code to use all characters in cell A1 as sheet names of the respective sheet.

Here is the Code:

Sub RenameWorksheet_Content_A1_2_Words()

Dim WS As Worksheet
Dim shtName
Dim newName As String
Dim i As Integer

For Each WS In Worksheets
With WS
If Trim(.Range("A1")) <> "" Then
shtName = Split(Trim(.Range("A1")), " ")
newName = shtName(0) & " " & shtName(1)
On Error GoTo ws_name_error
.Name = newName
GoTo done
repeat:
.Name = newName & i
GoTo done
ws_name_error:
i = i + 1
Resume repeat
End If
End With
On Error GoTo 0
done:
Next
End Sub

the reason it doesn't work is because you spec'd the delimiter as a space using split(). that function only does one thing.
you can't make it do anymore. the best workaround, IMO, is to create sheets that do you not have spaces in them. that is, after all,
protocol for all developers anyway. spaces create very bad issues. that's why SEO addresses on websites do not have spaces in them.
they have dashes (-) or underscores (_).

Originally Posted by CoSinus

Thank you for your help:
It gives me an error in this line:

i = i + 1

What is the error? Can you post a workbook to demonstrate that error?

And there is a Problem with sheetnames with only one word.

Did you take out the line where you are trying to SPLIT the cell using the space (as I posted above)?
If you copy the code I pasted this should not be a problem.

Some possible error for renaming sheet.

1) Can't use the name that already exists in the workbook.
2) Length must be less than 31 characters.
3) Use of invalid characters i.e. : ? \ [ ] / *

If you don't care about above then simply

Sub RenameWorksheet_Content_A1_2_Words()
    Dim WS As Worksheet
    For Each WS In Worksheets
        If WS.[a1] <> "" Then WS.Name = Replace(WS.[a1], " ", "_")
    Next
End Sub

As I mentioned already, length...

Sub RenameWorksheet_Content_A1_2_Words()
    Dim WS As Worksheet
    For Each WS In Worksheets
        If WS.[a1] <> "" Then WS.Name = Replace(LEFT(WS.[a1],30), " ", "_")
    Next
End Sub

Thank you all for your help.
The solution in the end is perfect.

I am very grateful.

This is atypical human error when you did not enable Option Explicit to force the variables to be declared.
If you add Option Explicit at the top of the module, it will tell you the variable(s) that are not declared.

CoSinus

Refer to my Post#5 for Error renaming sheet.

Sub RenameWorksheet_Content_A1_ALL_Words()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Name = GetNewWSName(ws.Cells(1, 1).Value, ws.Name)
    Next
End Sub

Function GetNewWSName(ByVal wsName As String, myName As String) As String
    Dim i As Long, temp As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[:?\\\[\]/*]+"
        wsName = .Replace(wsName, "")
    End With
    wsName = Left$(wsName, 31)
    If wsName = myName Then GetNewWSName = myName: Exit Function
    If Evaluate("isref('" & wsName & "'!a1)") Then
        wsName = Left$(wsName, 27)
        Do
            i = i + 1
            temp = wsName & Format$(i, "_000")
            If (temp = myName) + (Not Evaluate("isref('" & temp & "'!a1)")) Then
                GetNewWSName = temp: Exit Do
            End If
        Loop
    Else
        GetNewWSName = wsName
    End If
End Function

Thank you very much.
I have been looking for a solution to this problem for so long.

THANKS!!!