Auto Naming Worksheets

[Will Cendrowski]

I have a workbook that has multiple worksheets. I would like to name the
worksheets and used a macro that I found in one of the threads:

Sub name_sheets()
For Each ws In Worksheets
ws.Name = ws.Range("A1").Value
Next
End Sub

The macro works great but their are some A1 cells in some of the sheets that
will not be named until the user completes a master form and the name is
filtered to the corresponding sheet.

Being new to the macro world, how can I have the macro skip the sheets that
have a 'null' value in A1?

 

[Jacob Skaria]

Sub name_sheets()
For Each ws In Worksheets
If Trim(ws.Range("A1")) <> "" ws.Name = ws.Range("A1").Value
Next
End Sub


If this post helps click Yes

 

[Mike H]

Hi,

A null value will generate an error so use this

Sub name_sheets()
On Error Resume Next
For Each ws In Worksheets
ws.Name = ws.Range("A1").Value
Next
End Sub

Mike

 

[Dave Peterson]

Check replies at your post from yesterday.

I have a workbook that has multiple worksheets. I would like to name the
worksheets and used a macro that I found in one of the threads:

Sub name_sheets()
For Each ws In Worksheets
ws.Name = ws.Range("A1").Value
Next
End Sub

The macro works great but their are some A1 cells in some of the sheets that
will not be named until the user completes a master form and the name is
filtered to the corresponding sheet.

Being new to the macro world, how can I have the macro skip the sheets that
have a 'null' value in A1?

 

[Jacob Skaria]

You can use the worksheet change event so that the tab name can be renamed as
an when the user change the cell A1 in the sheet.. From workbook press
Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search
for the workbook name and click on + to expand it. Within that you should see
the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code pane.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count = 1 And Target.Address = "$A$1" Then
Application.EnableEvents = False
If Trim(Target.Text) <> "" Then Sh.Name = Trim(Range("A1"))
Application.EnableEvents = True
End If
End Sub

If this post helps click Yes