I have my criteria and fields set on a worksheet. Once someone pulls
up the sheet (tab) I would like for them to enter a date in cell A1.
Based on the date entered (once it is entered), I would like to (A)
automatically update the name of the current sheet to the month & year
given in A1; (B) create another sheet (copying the entire existing
sheet - except A1) named for the next month.

Example:
if someone enters a date 6/09/2007 in A1.

Macro would:
Rename current sheet June 07
Create a copy of the current sheet.
Name the copy July 07.

Any suggestions?

Right click sheet tabview codeinsert this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
mc = Target
ActiveSheet.Name = "x"
Sheets(ActiveSheet.Index + 1).Name = "xx"
ActiveSheet.Name = Format(mc, "mmm yy")
x = DateSerial(Year(mc), Month(mc) + 1, 1)
Sheets(ActiveSheet.Index + 1).Name = Format(x, "mmm yy")
End If
End Sub

--
Don Guillett
SalesAid Software

"J.W. Aldridge" wrote in message
oups.com...

I have my criteria and fields set on a worksheet. Once someone pulls
up the sheet (tab) I would like for them to enter a date in cell A1.
Based on the date entered (once it is entered), I would like to (A)
automatically update the name of the current sheet to the month & year
given in A1; (B) create another sheet (copying the entire existing
sheet - except A1) named for the next month.

Example:
if someone enters a date 6/09/2007 in A1.

Macro would:
Rename current sheet June 07
Create a copy of the current sheet.
Name the copy July 07.

Any suggestions?

A RE-read suggests that you may want this in the ThisWorkbook module instead
to do from any sheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo nogood
If Target.Address = "$A$1" Then
mc = Target
ActiveSheet.Name = "xxxx"
ActiveSheet.Name = Format(mc, "mmm yy")
x = DateSerial(Year(mc), Month(mc) + 1, 1)
Sheets.Add after:=ActiveSheet
ActiveSheet.Name = Format(x, "mmm yy")
End If
Exit Sub
nogood:
MsgBox "have one already"
End Sub


--
Don Guillett
SalesAid Software

"J.W. Aldridge" wrote in message
oups.com...

I have my criteria and fields set on a worksheet. Once someone pulls
up the sheet (tab) I would like for them to enter a date in cell A1.
Based on the date entered (once it is entered), I would like to (A)
automatically update the name of the current sheet to the month & year
given in A1; (B) create another sheet (copying the entire existing
sheet - except A1) named for the next month.

Example:
if someone enters a date 6/09/2007 in A1.

Macro would:
Rename current sheet June 07
Create a copy of the current sheet.
Name the copy July 07.

Any suggestions?

Thanx for the above...

Only one problem...

Would like to copy the current/original sheet.

The sheet generated should be a copy of the original sheet - with A1
blank (but just named for the next month).

Is this possible?

As always, THANX!

activesheet.copy

--
Don Guillett
SalesAid Software

"J.W. Aldridge" wrote in message
oups.com...
Thanx for the above...

Only one problem...

Would like to copy the current/original sheet.

The sheet generated should be a copy of the original sheet - with A1
blank (but just named for the next month).

Is this possible?

As always, THANX!