Name of sheet is based on cell value

ryan0521

Board Regular
Joined
Dec 7, 2016
Messages
79
Happy new year to all,

Can you help me to name sheets in workbook based on cell value specifically (J2).

Thank you in advance.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

YasserKhalil

Well-known Member
Joined
Jun 24, 2010
Messages
815
May be
Code:
Sub Test()    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Range("J2").Value <> "" Then ws.Name = ws.Range("J2").Value
    Next ws
End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,712
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Try

Code:
Sub MM1()
ActiveSheet.Name = Range("J2").Value
End Sub
 

ryan0521

Board Regular
Joined
Dec 7, 2016
Messages
79
Do I really need to run the code first? before it will rename the sheet?
 

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Do you want it to automatically rename the sheet any time cell J2 is changed?
 

YasserKhalil

Well-known Member
Joined
Jun 24, 2010
Messages
815

ADVERTISEMENT

Try this in worksheet module
Code:
Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Address = "$J$2" Then
        Me.Name = Range("J2").Value
    End If
End Sub

Or if you need to apply on any sheet in the workbook try this in workbook module
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address = "$J$2" Then
        Sh.Name = Range("J2").Value
    End If
End Sub
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,712
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Place this in the sheet module

Private Sub worksheet_Change(ByVal target As Range)
If Intersect(target, Range("J2")) Is Nothing Then Exit Sub
If Range("J2").Value <> "" Then ActiveSheet.Name = Range("J2").Value
End Sub
 

ryan0521

Board Regular
Joined
Dec 7, 2016
Messages
79
It works, but (J2) of sheet2 is link to sheet1 when I change the value of j2 in sheet1, sheet name for sheet1 changed but on sheet2 does not.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,712
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I think maybe it's time you gave us ALL the required information....Each time we give you a solution, you move the goal posts !!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,561
Messages
5,596,849
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top