Rename sheet based on cell on another sheet within the same workbook

[itsrich]

I use the same Excel file over and over. I just rename it and use it again.

Cell F5 on sheet "Work Sheet" has TXSP190SC-T in it. Sheet "(this name changes) Parts List" is currently in the Workbook. The component name for this sheet is Boat_01 (I named such in my efforts to figure this all out).

So...

I want to rename the sheet currently named "OLV165SC Parts List" to "TXSP190SC-T Parts List" using the cell contents from "Work Sheet" cell F5 = TXSP190SC-T. F5 will change every time I reuse the file.

TY for sharing your knowledge!

 

[DanteAmor]

Do you have a formula in F5?
Or is it a value that you change manually?
Do you want the sheet name to be changed automatically or by pressing a button?

 

[itsrich]

No formula. Changing automatically would be great! Thought to help me in my learning, can you post both auto and button so I can see the difference. I do not wish to impose though. TYVM!

 

[DanteAmor]

Changing automatically

Right click the tab of the sheet ("Work Sheet") you want this to work, select view code and paste the code into the window that opens up.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Target.Address(0, 0) = "F5" Then
    If Target.Value = "" Then Exit Sub
    Dim sh As Worksheet
    Dim exists As Boolean
    Dim suf As String
    
    suf = "Parts List"
    For Each sh In Sheets
      If LCase(Right(sh.Name, 10)) = LCase(suf) Then
        sh.Name = Target.Value & " " & suf
        exists = True
        Exit For
      End If
    Next
  End If
  If exists Then
    MsgBox "Updated name"
  Else
    MsgBox "Sheet with suffix 'Parts list'", vbExclamation, "Does not exist"
  End If
End Sub

__________________________________________________________________
With a button

Sub ChangeName()
  Dim sh As Worksheet
  Dim exists As Boolean
  Dim suf As String
  
  If Range("F5").Value = "" Then Exit Sub
  suf = "Parts List"
  For Each sh In Sheets
    If LCase(Right(sh.Name, 10)) = LCase(suf) Then
      sh.Name = Range("F5").Value & " " & suf
      exists = True
      Exit For
    End If
  Next
  If exists Then
    MsgBox "Updated name"
  Else
    MsgBox "Sheet with suffix 'Parts list'", vbExclamation, "Does not exist"
  End If
End Sub

 

[itsrich]

So if I read the code correctly, you look through all the sheet names until you found a match for "Parts List" and then replace it. WOW, I was looking in the wrong direction. Thank you for enlightening me! This a simple and elegant way to reach the goal!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.