I have a workbook with multiple sheets and want them to be renamed based on the value of a certain cell, e.g A5 in each sheet.

I want to add a prompt so that the user can select a cell, e.g B6, and then it will use that cell from all of the sheets to rename.

This is the code I have but it doesn't work.

Sub name()


Dim ws As Worksheet
Dim name_location As Range

'Location prompt
name_location = Application.InputBox _
            (Prompt:="What cell would you like to use as your sheet name?", _
            Title:="Cell for sheet name", Type:=8)


For Each ws In Worksheets
On Error Resume Next

            
            
'Location of name
ws.name = ws.Range(name_location).Value


If Err.Number <> 0 Then

Err.Clear
MsgBox "You are trying to name a sheet using an illegal character," & vbCrLf & _
"or with a duplicate name as another sheet.", 48, "Cannot continue, naming rules were violated."
Exit Sub
End If
Next ws


End Sub
Also, would there be a way to use the cell value to replace part of the current sheet name?

E.g my sheet is currently called sheet 1 (1), i want the last 3 charachters removed, and then the value of cell B6 added to the name instead of replaced.

Thanks