Worksheet Name
 

I would like to have a worksheet automatically names itself whatever is in
cell a1....

What I would really like to do --

I need to create a payment request sheet for every 14 days... I have to show
the date at the top of each form. Rather than going in and typing the date I
want to see there, I would like each tab (and cell a1) to automatically
create itself... for instance, if sheet 1 is "9-7-2007", sheet 2 should read
"9-21-2007".

Any thoughts?

Worksheet Name
 

Takes some code to do this. The code goes into the workbook code module. To
get to the right place: right-click on the Excel icon immediately to the left
of the word File in the Excel menu toolbar. Then choose [View Code] from the
list that pops up. Copy the code below and paste it into the module.

There is at least one restriction imposed by one of these routines - you
can't use the left parenthesis character [ ( ] as part of a worksheet name.
The code will see that and try to rename any sheet with it in the name.


Private Sub Workbook_NewSheet(ByVal Sh As Object)
'this will generally work when you use Insert | Worksheet
Dim anySheet As Worksheet
Dim latestDate As Date

latestDate = "1/1/1910" ' any very early date will do
For Each anySheet In Worksheets
If anySheet.Name < Sh.Name Then
'not our new sheet, check for date
If Not IsEmpty(anySheet.Range("A1")) And _
IsDate(anySheet.Range("A1")) Then
If anySheet.Range("A1") latestDate Then
latestDate = anySheet.Range("A1")
End If
End If
End If
Next ' examine next possible worksheet
'add 14 days to latest date
latestDate = latestDate + 14
'set format as d-Mon-YY
Sh.Range("A1").NumberFormat = "[$-409]d-mmm-yy;@"
Sh.Range("A1") = latestDate
Sh.Name = Sh.Range("A1").Text
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'this method prevents you from using the ( character as
'part of a sheet name because it's looking for that
'character to determine if this is a copy of another
'worksheet.
'
'this works when copying sheets.
'
Dim newDate As Date
If InStr(Sh.Name, "(") Then
'may be a sheet we need to rename
'check if there is a date in A1
If Not IsEmpty(Range("A1")) And _
IsDate(Range("A1")) Then
Range("A1") = Range("A1") + 14
Sh.Name = Range("A1").Text
End If
End If
End Sub


"Difficult1" wrote:

I would like to have a worksheet automatically names itself whatever is in
cell a1....

What I would really like to do --

I need to create a payment request sheet for every 14 days... I have to show
the date at the top of each form. Rather than going in and typing the date I
want to see there, I would like each tab (and cell a1) to automatically
create itself... for instance, if sheet 1 is "9-7-2007", sheet 2 should read
"9-21-2007".

Any thoughts?

Worksheet Name
 

These two routines should cover most cases, one takes care of when you copy
an existing sheet (presumes you're copying the latest one), the other deals
with inserting a new worksheet into the workbook.

One restriction is imposed: you can't use the left parenthesis character [ (
] in a sheet name - the code will see it and try to rename it.

To put this code into the right place: right-click on the Excel icon to the
immediate left of the word File in the Excel menu toolbar. Choose [View
Code] from the list that appears. Copy the code below and paste it into the
module presented to you.


Private Sub Workbook_NewSheet(ByVal Sh As Object)
'this will generally work when you use Insert | Worksheet
Dim anySheet As Worksheet
Dim latestDate As Date

latestDate = "1/1/1910" ' any very early date will do
For Each anySheet In Worksheets
If anySheet.Name < Sh.Name Then
'not our new sheet, check for date
If Not IsEmpty(anySheet.Range("A1")) And _
IsDate(anySheet.Range("A1")) Then
If anySheet.Range("A1") latestDate Then
latestDate = anySheet.Range("A1")
End If
End If
End If
Next ' examine next possible worksheet
'add 14 days to latest date
latestDate = latestDate + 14
'set format as d-Mon-YY
Sh.Range("A1").NumberFormat = "[$-409]d-mmm-yy;@"
Sh.Range("A1") = latestDate
Sh.Name = Sh.Range("A1").Text
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'this method prevents you from using the ( character as
'part of a sheet name because it's looking for that
'character to determine if this is a copy of another
'worksheet.
'
'this works when copying sheets.
'
Dim newDate As Date
If InStr(Sh.Name, "(") Then
'may be a sheet we need to rename
'check if there is a date in A1
If Not IsEmpty(Range("A1")) And _
IsDate(Range("A1")) Then
Range("A1") = Range("A1") + 14
Sh.Name = Range("A1").Text
End If
End If
End Sub

"Difficult1" wrote:

I would like to have a worksheet automatically names itself whatever is in
cell a1....

What I would really like to do --

I need to create a payment request sheet for every 14 days... I have to show
the date at the top of each form. Rather than going in and typing the date I
want to see there, I would like each tab (and cell a1) to automatically
create itself... for instance, if sheet 1 is "9-7-2007", sheet 2 should read
"9-21-2007".

Any thoughts?

Worksheet Name
 

This worked great! Thanks a million!

"JLatham" wrote:

Takes some code to do this. The code goes into the workbook code module. To
get to the right place: right-click on the Excel icon immediately to the left
of the word File in the Excel menu toolbar. Then choose [View Code] from the
list that pops up. Copy the code below and paste it into the module.

There is at least one restriction imposed by one of these routines - you
can't use the left parenthesis character [ ( ] as part of a worksheet name.
The code will see that and try to rename any sheet with it in the name.


Private Sub Workbook_NewSheet(ByVal Sh As Object)
'this will generally work when you use Insert | Worksheet
Dim anySheet As Worksheet
Dim latestDate As Date

latestDate = "1/1/1910" ' any very early date will do
For Each anySheet In Worksheets
If anySheet.Name < Sh.Name Then
'not our new sheet, check for date
If Not IsEmpty(anySheet.Range("A1")) And _
IsDate(anySheet.Range("A1")) Then
If anySheet.Range("A1") latestDate Then
latestDate = anySheet.Range("A1")
End If
End If
End If
Next ' examine next possible worksheet
'add 14 days to latest date
latestDate = latestDate + 14
'set format as d-Mon-YY
Sh.Range("A1").NumberFormat = "[$-409]d-mmm-yy;@"
Sh.Range("A1") = latestDate
Sh.Name = Sh.Range("A1").Text
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'this method prevents you from using the ( character as
'part of a sheet name because it's looking for that
'character to determine if this is a copy of another
'worksheet.
'
'this works when copying sheets.
'
Dim newDate As Date
If InStr(Sh.Name, "(") Then
'may be a sheet we need to rename
'check if there is a date in A1
If Not IsEmpty(Range("A1")) And _
IsDate(Range("A1")) Then
Range("A1") = Range("A1") + 14
Sh.Name = Range("A1").Text
End If
End If
End Sub


"Difficult1" wrote:

I would like to have a worksheet automatically names itself whatever is in
cell a1....

What I would really like to do --

I need to create a payment request sheet for every 14 days... I have to show
the date at the top of each form. Rather than going in and typing the date I
want to see there, I would like each tab (and cell a1) to automatically
create itself... for instance, if sheet 1 is "9-7-2007", sheet 2 should read
"9-21-2007".

Any thoughts?