Hi

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

I expect the only way to do this is using the VB editor, but does anyone
know the code to achieve this?

Thanks
Simon

If cell A1 contains the date, then

Worksheets(1).Name = Year(Range("A1").Value)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Pieman" wrote in message
...
Hi

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

I expect the only way to do this is using the VB editor, but
does anyone
know the code to achieve this?

Thanks
Simon

ThisWorkbook.worksheets("Sheet1").Name = Year(Date)
ThisWorkbook.worksheets("Sheet21").Name = Year(Date)-1

HTH
--
AP

"Pieman" a écrit dans le message de
...
Hi

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

I expect the only way to do this is using the VB editor, but does anyone
know the code to achieve this?

Thanks
Simon

Hi

I tried the code you suggested in the VB editor for each sheet and nothing
changed. Could this be because there is already some extra code for this
sheet as follows:

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


"Ardus Petus" wrote:

ThisWorkbook.worksheets("Sheet1").Name = Year(Date)
ThisWorkbook.worksheets("Sheet21").Name = Year(Date)-1

HTH
--
AP

"Pieman" a écrit dans le message de
...
Hi

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

I expect the only way to do this is using the VB editor, but does anyone
know the code to achieve this?

Thanks
Simon

Where did you plce the code?
It should be within a Sub, in a Module.

NOT in the Worksheet's Code

HTH
--
AP

"Pieman" a écrit dans le message de
...
Hi

I tried the code you suggested in the VB editor for each sheet and nothing
changed. Could this be because there is already some extra code for this
sheet as follows:

OK, is this correct...

Public Sub Worksheet_Name()
ThisWorkbook.Worksheets("Sheet2").Name = Year(Date)
ThisWorkbook.Worksheets("Sheet3").Name = Year(Date) - 1
End Sub


"Ardus Petus" wrote:

Where did you plce the code?
It should be within a Sub, in a Module.

NOT in the Worksheet's Code

HTH
--
AP

"Pieman" a écrit dans le message de
...
Hi

I tried the code you suggested in the VB editor for each sheet and nothing
changed. Could this be because there is already some extra code for this
sheet as follows: