Auto Changing a Sheet Name based on cell value

Hello,
I currently have a workbook that I am trying to have the tab name reflect what is in the sheets cell "A1" for every sheet.  I currently have the code set up as below, however it requires that I click each tab in order to get it to change, is there a way to get it to do this function without having to click the sheet tab?

Private Sub Worksheet_Activate()
On Error Resume Next
Me.Name = Range("a1").Value
End Sub
christina RidingsAsked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
Can put code into the Workbook_Open() event that will iterate through the worksheets.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may have this sub-routine on a Standard Module and run this to rename all the sheets.

Sub ChangeSheetNames()
Dim ws As Worksheet
On Error Resume Next
For Each ws In Worksheets
   If ws.Range("A1").Value <> "" Then
      ws.Name = ws.Range("A1").Value
   End If
Next ws
End Sub

Open in new window

0
 
KoenConnect With a Mentor Change and Transition ManagerCommented:
Also maybe test your A1 value for illegal characters and length
(21 characters max, symbols not allowed are  \ ? / : [ ] *)
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Koen
On Error Resume Next would take care of all those issues.
Also the max characters allowed are 31 not 21.
0
 
KoenChange and Transition ManagerCommented:
I just use a function for files and sheets that removes the illegale characters...skipping it usually is not what the customer wants.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
As per the objection raised in Post ID: 41848750.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.