Change Only Certain Worksheet Names Using a List

Hi there!
I'm sure it can be done with Worksheet_Change event on the INPUTS worksheet.
Please upload a dummy file with the minimum info.

Ok...I've attached a sample file. You can see what I've done, and hopefully it makes sense what I'm wanting to do instead.

BTW...I'm not familiar with the selection_change function. So I'll look into that now.

Also, this is a workbook I'm passing off to other people, so simpler is always better, which is why I was hoping to automate.

As an outline design, use column A on the INPUT sheet for a list of the existing worksheet names and column B would, initially be blank ... but will be used for the new worksheet names.

Then you need a Worksheet Change event handler monitoring the cells in column B. When a cell changes in column B, check if the sheet in the corresponding cell in column A exists. If it doesn't, put a warning in column C and exit. If it does, change the sheet name from its existing value to the new name. You should then add the new name to the bottom of the list in column A, and delete the old name from the list.

Relatively straightforward. No code needed in any of the other sheets.

Regards, TMS

Even simpler! There is no need for 3 columns. You can take the old name (to be changed) with the selection_change event and change it on place.

this code under INPUTS code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim new_name As String
Dim ws As Worksheet

If Target.Column = 2 And Target <> "" Then
    Set ws = Sheets(old_name)
    ws.Name = Target.Value
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 And Target <> "" Then
    old_name = Target.Value
End If
End Sub

and this code in a module (insert Module)

Option Explicit

Public old_name As String

Glad to help! Please mark the thread as [SOLVED]. (and add reputation if you wish)

sure, replace both if target.column.... with

If Not Intersect(Target, Range("B3:B7")) Is Nothing And Target <> "" Then