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?

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?

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?

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?