Worksheet Tabs Names as Dates
 

Hi

Is it possible to change the name of a worksheet tab to show the current
year and another to show the previous year? If it helps, each sheet has the
correct year in one of the cells using the TODAY function.

Thanks
Simon

Worksheet Tabs Names as Dates
 

Unless you want to use a macro, naming a sheet is a manual action.

--
Regards,
Tom Ogilvy

"Pieman" wrote in message
...
Hi

Is it possible to change the name of a worksheet tab to show the current
year and another to show the previous year? If it helps, each sheet has
the
correct year in one of the cells using the TODAY function.

Thanks
Simon

Worksheet Tabs Names as Dates
 

Hi, yes I thought it would require VBA programming to work. Is there a way to
do this using VB coding?

Thanks
Simon

"Tom Ogilvy" wrote:

Unless you want to use a macro, naming a sheet is a manual action.

--
Regards,
Tom Ogilvy

"Pieman" wrote in message
...
Hi

Is it possible to change the name of a worksheet tab to show the current
year and another to show the previous year? If it helps, each sheet has
the
correct year in one of the cells using the TODAY function.

Thanks
Simon

Worksheet Tabs Names as Dates
 

Pieman

Sub Sheetname_cell()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
On Error Resume Next
sh.Name = sh.Range("A1").Value
'next lines cover duplicate names
If Err.Number 0 Then
MsgBox "Change the name of : " & sh.Name & " manually"
Err.Clear
End If
On Error GoTo 0
Next
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Mon, 6 Mar 2006 02:00:28 -0800, Pieman
wrote:

Hi, yes I thought it would require VBA programming to work. Is there a way to
do this using VB coding?

Thanks
Simon

"Tom Ogilvy" wrote:

Unless you want to use a macro, naming a sheet is a manual action.

--
Regards,
Tom Ogilvy

"Pieman" wrote in message
...
Hi

Is it possible to change the name of a worksheet tab to show the current
year and another to show the previous year? If it helps, each sheet has
the
correct year in one of the cells using the TODAY function.

Thanks
Simon

Worksheet Tabs Names as Dates
 

Thanks for your response. I entered your code as suggested and just changed
the cell reference to 'E2' but nothing happens. I am also using the following
code on the same sheet so am not sure if this is causing a problem:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E4:E6")) Is Nothing _
Or Target.Count < 1 _
Then Exit Sub
Application.EnableEvents = False
With Target
If .Value = "" Then
Range("E4:E6").Value = ""
Else
Select Case Target.Row
Case 4 'Weekly
Range("E5").Value = .Value * 52 / 12
Range("E6").Value = .Value * 52
Case 5 'Monthly
Range("E4").Value = .Value * 12 / 52
Range("E6").Value = .Value * 12
Case 6 'Yearly
Range("E4").Value = .Value / 52
Range("E5").Value = .Value / 12
End Select
End If
End With
Application.EnableEvents = True
End Sub

Pieman

"Gord Dibben" wrote:

Pieman

Sub Sheetname_cell()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
On Error Resume Next
sh.Name = sh.Range("A1").Value
'next lines cover duplicate names
If Err.Number 0 Then
MsgBox "Change the name of : " & sh.Name & " manually"
Err.Clear
End If
On Error GoTo 0
Next
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Mon, 6 Mar 2006 02:00:28 -0800, Pieman
wrote:

Hi, yes I thought it would require VBA programming to work. Is there a way to
do this using VB coding?

Thanks
Simon

"Tom Ogilvy" wrote:

Unless you want to use a macro, naming a sheet is a manual action.

--
Regards,
Tom Ogilvy

"Pieman" wrote in message
...
Hi

Is it possible to change the name of a worksheet tab to show the current
year and another to show the previous year? If it helps, each sheet has
the
correct year in one of the cells using the TODAY function.

Thanks
Simon