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 tab>view code>insert 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
(E-Mail Removed)
"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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
(E-Mail Removed)
"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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
(E-Mail Removed)
"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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!
>
>