• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

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
0
christina Ridings
Asked:
christina Ridings
  • 3
  • 2
2 Solutions
 
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
 
aikimarkCommented:
Can put code into the Workbook_Open() event that will iterate through the worksheets.
0
 
KoenCommented:
Also maybe test your A1 value for illegal characters and length
(21 characters max, symbols not allowed are  \ ? / : [ ] *)
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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
 
KoenCommented:
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now