dynamically name worksheets

[Mike Brearley]

The first worksheet in an excel workbook contains cells that match what the
names of the following worksheets need to be named. We have one master copy
and new files are created from this master daily. Each new copy changes the
cells and the names of the following worksheets. Currently we are manually
changing the names of the worksheets (21 of them) every single time. Is
there a way to have the names change dynamically when we put the new value
in the first worksheet? Something similar to the way we have the heading on
each sheet change by using ='FirstSheet'!A2

Thanks...

--
Posted 'as is'. If there are any spelling and/or grammar mistakes, they
were a direct result of my fingers and brain not being synchronized or my
lack of caffeine.

Mike Brearley

 

[Bernie Deitrick]

Mike,

Copy the code below, right click on the tab of "FirstSheet", select "View Code" and paste the code
into the window that appears.

This assumes that your sheet names are in cells A2:A22, and that the heading link on each sheet is
in cell C1 (the cell with the formula ='FirstSheet'!A2).

Change the addresses in the code to reflect your actual layout.

If you can't get it to work, I will send you a small example workbook - just contact me privately by
taking out the spaces and changing the dot to . in my email address.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim mySht As Worksheet
If Intersect(Target, Range("A2:A22")) Is Nothing Then Exit Sub

For Each mySht In ThisWorkbook.Worksheets
If mySht.Range("C1").Value <> mySht.Name Then
mySht.Name = mySht.Range("C1").Value
End If
Next mySht

End Sub

 

[Mike Brearley]

Works great...

Thanks...

--
Posted 'as is'. If there are any spelling and/or grammar mistakes, they
were a direct result of my fingers and brain not being synchronized or my
lack of caffeine.

Mike Brearley