RENAME SHEET WITH CELL VALUE UNTIL BRACKETS

[xenios]

Hi, hope somebody can help me.
Need to rename workbook sheets with cell value using macros, as it's about 300 sheets.
The problem is I have for example the name TEST1 (125) MY NAME, and need the sheet to be renamed only withe text before the 1st bracket.
Is there a way to do it with macros?

 

[VBE313]

Sub xenios()
Dim wsCount As Integer, I As Long
wsCount = ActiveWorkbook.Worksheets.Count
For I = 1 To wsCount
    Worksheets(I).Name = ExtractNthWord(Worksheets(I).Name, 1)
Next I
End Sub

Function ExtractNthWord(x As String, y As Integer) 'https://www.exceltip.com/tips/how-to-extract-nth-word-from-text-string-using-vba-in-microsoft-excel-2010.html
Dim word() As String, wordCount As Long
word = VBA.Split(x, " ")
wordCount = UBound(word)
If wordCount < 1 Or (y - 1) > wordCount Or y < 0 Then
    ExtractNthWord = ""
Else
    ExtractNthWord = word(y - 1)
End If
End Function

 

[Fluff]

Hi & welcome to MrExcel.
Which cells are the new names in & which cells are the current names in?

 

[xenios]

The name in the cell TEST1 (125) MY NAME
The name tha should have the sheet TEST1

Thank you

 

[xenios]

The name in the cell TEST1 (125) MY NAME
The name tha should have the sheet TEST1

Thank you

Run-time error 1004, what can it be?

 

[xenios]

Sub xenios()
Dim wsCount As Integer, I As Long
wsCount = ActiveWorkbook.Worksheets.Count
For I = 1 To wsCount
    Worksheets(I).Name = ExtractNthWord(Worksheets(I).Name, 1)
Next I
End Sub

Function ExtractNthWord(x As String, y As Integer) 'https://www.exceltip.com/tips/how-to-extract-nth-word-from-text-string-using-vba-in-microsoft-excel-2010.html
Dim word() As String, wordCount As Long
word = VBA.Split(x, " ")
wordCount = UBound(word)
If wordCount < 1 Or (y - 1) > wordCount Or y < 0 Then
    ExtractNthWord = ""
Else
    ExtractNthWord = word(y - 1)
End If
End Function

Run-time error 1004, what can it be?

 

[VBE313]

at what line are you getting the error at?

 

[Fluff]

You will need to have a list of the current sheet names & a list of the "TEST1 (125) MY NAME" values so we know what sheet should be renamed.
Do you have a list like that & if so where is it?

 

[xenios]

at what line are you getting the error at?

If you put only the part before function, the error is on ExtractNthWord. print screen attached.
If I put the whole thing error 1004, without showing where is the error

 

[VBE313]

that sub procedure is calling the function ExtractNthWord. You need to add that. Take a screenshot of what the error is showing when the function is inserted.