Naming a worksheet after a cell valu

[Jerry Eggleston]

I have a several worsheets in a workbook. I would like to be able t
run a macro that would rename many of the sheets based on the values i
a range of cells. If the values change then I would like to be able t
change the worksheet names with a manual macro.

Any help would be greatly appreciated




Jerr

 

[Jason Morin]

You can run a simple macro like this:

Sub ChangeName()
Dim ws As Worksheet
Dim i As Integer
i = 1
For Each ws In ThisWorkbook.Worksheets
With ws
If .Name <> "Sheet1" Then
.Name = Sheets("Sheet1") _
.Cells(i, "A")
End If
End With
i = i + 1
Next ws
End Sub

----
Where Sheet1 has the new worksheet names starting in A1.
But this macro does not contain any error trapping, and
will error if:

a) the total # of worksheets (not including Sheet1) does
not match the number of names in the list starting in A1.
b) Duplicate names in the list.

HTH
Jason
Atlanta, GA

 

[mzehr]

Hi Jerry,
Frank Kabel had posted the following procedure a few
months back, which might help:
Worksheet Name Change According to Cell Value
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then
Exit Sub
On Error GoTo CleanUp
application.enableevents = false
With Target
If .Value <> "" Then
Me.Name = .value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

if you are using month/date then use the following
activesheet.name = format(range("A1").value,"mmmm")

HTH