I have a workbook with sheets for the past 6 months, each named with the
3 letter abbreviation for the month. I currently use a routine each month
that copies the previous month's sheet to a new sheet and prepares that
sheet for the new month.

Two things I've noticed:

1) The Worksheet_Change code from the old sheet comes along with the
copy, which I want, but don't need any more in the old sheet. This
results in unnecessary file bloat.

2) The codename for the new sheet, as seen in VBE Project Explorer
becomes the abbreviation for the old sheet with an increment number
tacked on, ie Oct becomes Oct1, then increments on subsequent copies to
Oct2, Oct3, etc. instead of the new month's sheet name. My wish: Sheet
named Nov would show as Nov(Nov) in the Project Explorer.

What can I add to overcome these nuisances?

The code:

Sub New_Month()
Application.DisplayAlerts = False
ActiveSheet.Unprotect
If Sheets.Count = 6 Then Sheets(1).Delete
srcName = ActiveSheet.Name: tgtName = Format(CDate(srcName & "-2002") +
32, "mmm")
Sheets(srcName).Copy After:=Sheets(srcName): Sheets
(srcName).TextBoxes.Delete
ActiveSheet.Name = tgtName
Range("B5:C35,E5:E35,I5:M34").ClearContents
Range("G1") = tgtName
With ActiveSheet.Previous
Range("J5") = Application.Max(.Range("J5:J25").Value) + 1
Range("I3") = DateSerial(Year(.Range("I3")), Month(.Range("I3")) + 2, 0)
_
- Application.Max(0, Weekday(DateSerial(Year(.Range("I3")), _
Month(.Range("I3")) + 2, 0), 2) - 5)
Range("L3") = .Range("N35")
End With
Range("G2") = Format(CDate(Range("I4") + 3), "yyyy")
Range("B5").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=False, Scenarios:
=False
Application.DisplayAlerts = True
End Sub
--
David