Worksheet Tabs Names as Dates

P

Pieman

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
 
P

Pieman

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

Thanks
Simon
 
G

Gord Dibben

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
 
P

Pieman

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
 
Top