Rename part of sheet name vba

[muhleebbin]

Can someone help me figure out the code to rename just part of a sheet name while keeping the rest?

Current code is as follows but the original sheet name has a location description after the 4th character and would like to keep that. Plus this just tries to rename each sheet that contains A1 value to B1 value and runs into "The name is already taken error"

Thanks in advance for your assistance!

Sub RenameSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    With ws
        If Left(.Name, 4) = Sheets("Lists").Range("A1").Value Then .Name = Left(Sheets("Lists").Range("B1").Value, 4)
    End With
Next ws
End Sub

 

[HaHoBe]

Hi muhleebbin,

assuming both values are 4 characters try

Sub RenameSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  With ws
    If Left(.Name, 4) = Sheets("Lists").Range("A1").Value Then _
         .Name = Replace(.Name, Sheets("Lists").Range("A1").Value, Sheets("Lists").Range("B1").Value, 1)
  End With
Next ws
End Sub

Ciao,
Holger

 

[muhleebbin]

Such a simple solution!

Thank you Holger!