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?

 

[Fluff]

Or do you mean you want to rename each sheet with what is in A7 on that sheet?
If so try

Sub xenios2()
   Dim Ws As Worksheet
   
   For Each Ws In WorkSheets
      Ws.Name = Split(Ws.Range("A7").Value, " (")(0)
   Next Ws
End Sub

 

[xenios]

But that sheet already has the correct name.

Yes I send the final result, so far it was done manually. Initially all the sheets have names Sheet1, Sheet2, Sheet3/

 

[Fluff]

Did you see post#21?

 

[xenios]

Or do you mean you want to rename each sheet with what is in A7 on that sheet?
If so try

Sub xenios2()
   Dim Ws As Worksheet
  
   For Each Ws In WorkSheets
      Ws.Name = Split(Ws.Range("A7").Value, " (")(0)
   Next Ws
End Sub

Thank you. Tried this one.
The following error occurred (1004) attached. And the string it doesn't like is attached with debug name,

 

[xenios]

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.

Attaching you the exact string with the error.
Worksheets(I).name = ExtractNthWord(Worksheets(I).name, 1)

 

[xenios]

Attaching you the exact string with the error.
Worksheets(I).name = ExtractNthWord(Worksheets(I).name, 1)

I have the name allways in cell A7, may be it will help.

 

[xenios]

at what line are you getting the error at?

Error is at.
Worksheets(I).name = ExtractNthWord(Worksheets(I).name, 1)

Also all the names are in cell A7. May it may help.

 

[mehidy1437]

Or do you mean you want to rename each sheet with what is in A7 on that sheet?
If so try

Sub xenios2()
   Dim Ws As Worksheet
 
   For Each Ws In WorkSheets
      Ws.Name = Split(Ws.Range("A7").Value, " (")(0)
   Next Ws
End Sub

Hi, I'm sorry if its wrong to ask here.
I just check this & it's okay for me, but If I have the same name on different sheets then it stops.
How to add an auto-increment number at the end for the same name?

 

[xenios]

Hi, I'm sorry if its wrong to ask here.
I just check this & it's okay for me, but If I have the same name on different sheets then it stops.
How to add an auto-increment number at the end for the same name?

Sorry, I don't have the answer for you. But can you please send me an example of your sheet names, may be I can figure out what is wrong with mine.
Thank you.

 

[mehidy1437]

My names are simple name, no brackets or anything special.