VBA code to change the sheet name based on cell value

[DE_Tx]

I've read several other posts and am not getting my code quite right.
I need to change a sheet name (daily) based on the value in another cell on another worksheet. I read the post here regarding changing sheet name with a date suffix so I am basing my code on that.
I've placed this code within the sheet module I am trying to rename (which happens to be Sheet22).
The place where I have stored the value for the sheet name (which will change daily dynamically) is on Sheet17 in cell L17.

Private Sub Rpt1_sheetrename()
ActiveSheet.Name = Worksheets("Sheet17").Range("L17")
End Sub

I have also read the article by Tom Urtis and I get the general concept but the code is much more than I need.
I'm looking for something simple that will change the sheet name based on input from a cell value that will change dynamically based on data from another range.

The code above is giving me a Run time error 9: subscript out of range

Any assistance would be greatly appreciated.

TcO

 

[PCosta87]

Hi,

Since you want to rename the sheet based on the value of another sheet's cell, I would suggest a Worksheet_Change event...

With it, each time the value of that particular cell is changed, it will change the other sheet's name immediately.

So, place the following in the code of your Sheet17

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("L17")) Is Nothing Then
        Sheets(1).Name = Target
    End If

End Sub

and replace the 1 in "Sheets(1)" with the index number of the sheet which name you want to change. Alternatively use its code name as seen in the Project Explorer of the VBA window

 

[DE_Tx]

Thanks, PCosta. I hadn't thought to go from Sheet17 (tables) rather than Sheet22 (new sheet).
There are going to be a total of 4 sheets that will need to be updated each day. Sheet name references will be in L17:L20.
So, would I stack four of these If Not...End If statements under each other?

 

[PCosta87]

Thanks, PCosta. I hadn't thought to go from Sheet17 (tables) rather than Sheet22 (new sheet).
There are going to be a total of 4 sheets that will need to be updated each day. Sheet name references will be in L17:L20.
So, would I stack four of these If Not...End If statements under each other?

Hi,

Sort of...

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("L17:L20")) Is Nothing Then
        Sheets(1).Name = Range("L17")
        Sheets(2).Name = Range("L18")
        Sheets(3).Name = Range("L19")
        Sheets(4).Name = Range("L20")
    End If

End Sub

Just make sure to always have unique names in those 4 cells... and obviously they can't be empty. Each of the mentioned will cause the code to break.
Perhaps you should consider adding a condition to display some sort of alert if there are duplicates or empty cells, otherwise you should be ok!

 

[Marc L]

Hi !

PCosta87 code revisited including controls for any empty cell
or already existing worksheet name (edit v2) :​

Private Sub Worksheet_Change(ByVal Target As Range)
         Dim Rg As Range
    For Each Rg In Intersect([L17:L20], Target)
          If Rg.Value > "" Then If Not Evaluate("ISREF('" & Rg.Value & "'!A1)") Then Worksheets(Rg.Row - 16).Name = Rg.Value
    Next
End Sub

Do you like it ? So thanks to click on bottom right Like !
​