Worksheet Names as a Date

P

Pieman

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
 
C

Chip Pearson

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
 
A

Ardus Petus

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

HTH
 
P

Pieman

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
 
A

Ardus Petus

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

NOT in the Worksheet's Code

HTH
 
P

Pieman

OK, is this correct...

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