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]

@xenios
What was the value in A7 when you got the error?

@mehidy1437
Please do not hijack other members threads.

 

[mehidy1437]

@Fluff Noted & thanks.

 

[xenios]

@xenios
What was the value in A7 when you got the error?

@mehidy1437
Please do not hijack other members threads.

Actually yes, you are right. Finally it worked, but stopped at the point when the name is too long ALOE BOUTIQUE & SUITES Adults only 16+ (5 Star Hotel) Almyrida - Chania - Crete
Is it possible in the case when it's too long to take only fist 2 words?

Also realized will have the same problem with @mehidy1437
some name are identical, and in this case if it can add numer 1, 2, 3..

Or have to create another thread?

 

[Fluff]

How about

Sub xenios2()
   Dim Ws As Worksheet
   Dim Nme As String
   
   With CreateObject("scripting.dictionary")
      For Each Ws In WorkSheets
         Nme = Split(Ws.Range("A7").Value, " (")(0)
         If Len(Nme) > 31 Then Nme = Split(Nme, " ", 3)(0) & " " & Split(Nme, " ", 3)(1)
         If Not .Exists(Nme) Then
            .Add Nme, 0
            Ws.Name = Nme
         Else
            .Item(Nme) = .Item(Nme) + 1
            Ws.Name = Nme & .Item(Nme)
         End If
      Next Ws
   End With
End Sub

 

[xenios]

How about

Sub xenios2()
   Dim Ws As Worksheet
   Dim Nme As String
  
   With CreateObject("scripting.dictionary")
      For Each Ws In WorkSheets
         Nme = Split(Ws.Range("A7").Value, " (")(0)
         If Len(Nme) > 31 Then Nme = Split(Nme, " ", 3)(0) & " " & Split(Nme, " ", 3)(1)
         If Not .Exists(Nme) Then
            .Add Nme, 0
            Ws.Name = Nme
         Else
            .Item(Nme) = .Item(Nme) + 1
            Ws.Name = Nme & .Item(Nme)
         End If
      Next Ws
   End With
End Sub

Great! it works! Thank you!

I have another 2 questions. If i have to create new question, please let me know.

1. I have in a lot of cases when the text in on a sheet is not visible till the end, and I have to drag the cell manually for the text to be visible. May be there's is also a code for that? The example it attached.
2. Is it possible to create summary list with hyperlinks of all the sheets, but the names to be taken from cell A7 and until brackets.
So ALOE BOUTIQUE & SUITES Adults only 16+ (5 Star Hotel) Almyrida - Chania - Crete in the list to look like ALOE BOUTIQUE & SUITES Adults only 16+

 

[Fluff]

Both of those should be possible, but you will need to start a new thread for each.
Thanks

 

[xenios]

Great! it works! Thank you!

I have another 2 questions. If i have to create new question, please let me know.

1. I have in a lot of cases when the text in on a sheet is not visible till the end, and I have to drag the cell manually for the text to be visible. May be there's is also a code for that? The example it attached.
2. Is it possible to create summary list with hyperlinks of all the sheets, but the names to be taken from cell A7 and until brackets.
So ALOE BOUTIQUE & SUITES Adults only 16+ (5 Star Hotel) Almyrida - Chania - Crete in the list to look like ALOE BOUTIQUE & SUITES Adults only 16+

Thank you!

 

[Fluff]

You're welcome.