Excel: Name sheet tab from cell value in another sheet

Question

I can successfully name my sheet tab based on a cell reference in the same sheet with the following VBA code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Set Target = Range("A1")
    If Target = "" Then Exit Sub
    On Error GoTo Badname
    ActiveSheet.Name = Left(Target, 31)
    Exit Sub
Badname:
    MsgBox "Please revise the entry in A1." & Chr(13) _
    & "It appears to contain one or more " & Chr(13) _
    & "illegal characters." & Chr(13)
    Range("A1").Activate
End Sub

However, I am struggling to change the sheet tab based on a cell reference in the title sheet. I have tried simply replacing the range with the reference from the desired sheet, Range("Keywords!A1"), but this does not seem to work.

Any suggestions to get around this would be hugely appreciated.

Taosique · Accepted Answer · 2016-08-10 11:24:13Z

4

Use this to reference another worksheet

Worksheets("Keywords").Range("A1").Value

answered Aug 10, 2016 at 11:24

Taosique

7,9143 gold badges33 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Edwards Over a year ago

Just to add further context, you can use Sheets as well. This explains the difference quite well. For this example though it doesn't matter.

Collectives™ on Stack Overflow

Excel: Name sheet tab from cell value in another sheet

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related