CODEWITHSUNDEEP
excelvba
user16446331
user16446331

Reputation: 3

Dynamically changing sheet name to a value of a cell

I already found the below VBA which works, but it pulls the value of a cell within the sheet that I'm trying to change the name of, however, what I'm looking for is to reference a cell in a master sheet or a specific sheet lets say Sheet1, what needs to change in the VBA below to pull A1 from Sheet1 instead of the active sheet?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("A1")
If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub

Thanks alot, Lin

Upvotes: 0

Views: 171

Answers (1)

Tarik
Tarik

Reputation: 11209

You can use the following code:

Dim sheetname As String
sheetname = "xxxxxxx"
Dim mastersheetname as String
mastersheetname = "yyyyyyy"
Sheets(sheetname).Name = Sheets(mastersheetname).Range("A1").Value

You would be better off using named cells to avoid hardcoding cell addresses such as A1

Upvotes: 1

Related Questions