No formulas working to rename sheets within workbook

[JTown]

I have a workbook with several sheets that I am simply wanting to have renamed based on the value entered in cells on one of those sheets. For some reason, no basic formula nor VBA codes are working. Currently, I can only manually rename the sheet and it will display whatever is typed even if it is a formula.
Specifically, I have a sheet “Input” in which cells B8:B16 contain names. I want those values to then be the names of the sheets. So Sheet11 should be named after B8 on Input, Sheet12 named after B9 on Input, and so on.
I’ve tried entering =Input!B8 on the sheet by right clicking the current tab name and selecting “Rename”. This has resulted in =Input!B8 being displayed.
Prior to attempting some VBA codes myself, I checked to see if there was any other codes that could be interfering and when I selected View Code on each sheet there did not appear to be any active codes or modules.
I’ve also ensured that the cells I’m trying to reference are formatted as Text. Still no success.
I’ve also tried a few different codes in VBA that I found searching online, some even from other posts in this community, but none have worked.
I feel like I’m missing something simple. This is driving me crazy. Any help is greatly appreciated. Thank you.

 

[Joe4]

Welcome to the Board!

You cannot use formula in sheet tab names when trying to enter them manually.
You want Worksheet_Change event procedure VBA code, which is code that will run automatically when a cell is manually updated.

Go to your "Input" sheet, right-click on the sheet tab name at the bottom of your screen, select "View Code", and paste this VBA code in the VB Editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

'   Exit if entry is blank
    If Target.Value = "" Then Exit Sub

'   See if update to B8:B16
    If Intersect(Target, Range("B8:B16")) Is Nothing Then Exit Sub
    
'   Update appropriate sheet with name from cell
    Sheets(Target.Row + 3).Name = Target.Value

End Sub

As you enter values in B8:B16 on the Input sheet, it will automatically rename sheets 11, 12, ..., 19.

 

[JTown]

Welcome to the Board!

You cannot use formula in sheet tab names when trying to enter them manually.
You want Worksheet_Change event procedure VBA code, which is code that will run automatically when a cell is manually updated.

Go to your "Input" sheet, right-click on the sheet tab name at the bottom of your screen, select "View Code", and paste this VBA code in the VB Editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

'   Exit if entry is blank
    If Target.Value = "" Then Exit Sub

'   See if update to B8:B16
    If Intersect(Target, Range("B8:B16")) Is Nothing Then Exit Sub
   
'   Update appropriate sheet with name from cell
    Sheets(Target.Row + 3).Name = Target.Value

End Sub

As you enter values in B8:B16 on the Input sheet, it will automatically rename sheets 11, 12, ..., 19.

Thank you for the welcome and quick response!
Sadly, this did not work either. I will attach a screenshot of the error I received.
I also noticed something strange with the sheet names/order. They are not in sequence and appear to skip some numbers, although all the sheets I need/want are currently in the document. This could be a result of a prior users modifications I imagine. You can see the sheets (and my desired labeling) on the left side of the photo.

 

[Joe4]

y, this did not work either. I will attach a screenshot of the error I received.
I also noticed something strange with the sheet names/order. They are not in sequence and appear to skip some numbers, although all the sheets I need/want are currently in the document. This could be a result of a prior users modifications I imagine. You can see the sheets (and my desired labeling) on the left side of the photo.

I do not see any attached images at all. It sounds like you may have messed with the order of the sheets along the way, so the sheets are not in the same order as their sheet index numbers, and possibly you deleted some along the way.

If the sheets you want to rename are named like "Sheet11" and "Sheet12", then you could change this line:

    Sheets(Target.Row + 3).Name = Target.Value

to this:

    Sheets("Sheet" & (Target.Row + 3)).Name = Target.Value

But note that will only work once for each sheet, as once the sheet in no longer named "Sheet11", it will not be able to be re-named again.

 

[JTown]

I do not see any attached images at all. It sounds like you may have messed with the order of the sheets along the way, so the sheets are not in the same order as their sheet index numbers, and possibly you deleted some along the way.

If the sheets you want to rename are named like "Sheet11" and "Sheet12", then you could change this line:

    Sheets(Target.Row + 3).Name = Target.Value

to this:

    Sheets("Sheet" & (Target.Row + 3)).Name = Target.Value

But note that will only work once for each sheet, as once the sheet in no longer named "Sheet11", it will not be able to be re-named again.

Ok, so I see that the image did not upload and I can try again. However, after noticing the sheets out of order I suspected another user may have made changes that were causing a disruption. So I have copied the sheets I wanted into a new excel file so that all sheets are in order. This time, I am wanting to rename sheets 4 through 12 using cells B8:B16 from the Input sheet (sheet2). I tried using the previously mentioned code without success, and altered “Target.Row + 3” to “Target.Row - 4” without success.

 

[Joe4]

Which sheets exactly are you trying to rename?
Which sheet does cell B8 correspond with?

 

[JTown]

Which sheets exactly are you trying to rename?
Which sheet does cell B8 correspond with?

I’m trying to rename sheets 4-12 using the information from sheet2 B8:B16. So sheet2 B8 = name of sheet4, sheet2 B9 = name of sheet5, and so on

 

[Joe4]

Then try this variation:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

'   Exit if entry is blank
    If Target.Value = "" Then Exit Sub

'   See if update to B8:B16
    If Intersect(Target, Range("B8:B16")) Is Nothing Then Exit Sub
    
'   Update appropriate sheet with name from cell
    Sheets(Target.Row - 4).Name = Target.Value

End Sub

 

[JTown]

Then try this variation:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

'   Exit if entry is blank
    If Target.Value = "" Then Exit Sub

'   See if update to B8:B16
    If Intersect(Target, Range("B8:B16")) Is Nothing Then Exit Sub
   
'   Update appropriate sheet with name from cell
    Sheets(Target.Row - 4).Name = Target.Value

End Sub

Awesome, thank you Joe4, that worked finally! I appreciate your help, have a great day!

 

[Joe4]

You are welcome.
Glad I was able to help!