2 Copy Sheet ?'s
 

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

2 Copy Sheet ?'s
 

David,

To delete code, visit Chip Pearson';s site for details
http://www.cpearson.com/excel/vbe.htm

To change the sheet code name use

With Sheets(srcName)
.Parent.VBProject.VBComponents(.CodeName) _
.Properties("_CodeName") = "abc"
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"David Turner" wrote in message
...
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

2 Copy Sheet ?'s
 

Bob Phillips wrote

Hi, Bob
Thanks for the reply.

To change the sheet code name use

With Sheets(srcName)
.Parent.VBProject.VBComponents(.CodeName) _
.Properties("_CodeName") = "abc"
End With

Actually that changes the old sheet, but led to this solution:
With Sheets(tgtName)
.Parent.VBProject.VBComponents(.CodeName) _
.Properties("_CodeName") = tgtName
End With

To delete code, visit Chip Pearson';s site for details
http://www.cpearson.com/excel/vbe.htm

Heading there now, since this is the more critical of the two.

Thanks

--
David

2 Copy Sheet ?'s
 

Bob Phillips wrote

To delete code, visit Chip Pearson';s site for details
http://www.cpearson.com/excel/vbe.htm

Actually couldn't find anything there directly concerning removal of sheet
code, but a Google search ("sheet code module" delete group:*excel) led to
a reply from that added to my With Sheets(srcName) section
and it works:

With .Parent.VBProject.VBComponents(.CodeName)
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
End With

Thought you'd like to know, and thanks for leading the way.

--
David

2 Copy Sheet ?'s
 

Haven't checked that page out in a while, but I thought it had details on
deleting code. Thanks for updating us.

Bob

"David Turner" wrote in message
...
Bob Phillips wrote

To delete code, visit Chip Pearson';s site for details
http://www.cpearson.com/excel/vbe.htm

Actually couldn't find anything there directly concerning removal of sheet
code, but a Google search ("sheet code module" delete group:*excel) led to
a reply from that added to my With Sheets(srcName)
section
and it works:

With .Parent.VBProject.VBComponents(.CodeName)
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
End With

Thought you'd like to know, and thanks for leading the way.

--
David

2 Copy Sheet ?'s
 

Well, your right, that that particular code isn't there, but there is enough
information that, in most cases, one could easily achieve the desired
functionality.

It is good that you have figured out how to seach google.

--
Regards,
Tom Ogilvy

David Turner wrote in message
...
Bob Phillips wrote

To delete code, visit Chip Pearson';s site for details
http://www.cpearson.com/excel/vbe.htm

Actually couldn't find anything there directly concerning removal of sheet
code, but a Google search ("sheet code module" delete group:*excel) led to
a reply from that added to my With Sheets(srcName)
section
and it works:

With .Parent.VBProject.VBComponents(.CodeName)
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
End With

Thought you'd like to know, and thanks for leading the way.

--
David

2 Copy Sheet ?'s
 

It has plenty on deleting code. But not the specific code David needed.

--
Regards,
Tom Ogilvy

Bob Phillips wrote in message
...
Haven't checked that page out in a while, but I thought it had details on
deleting code. Thanks for updating us.

Bob

"David Turner" wrote in message
...
Bob Phillips wrote

To delete code, visit Chip Pearson';s site for details
http://www.cpearson.com/excel/vbe.htm

Actually couldn't find anything there directly concerning removal of
sheet
code, but a Google search ("sheet code module" delete group:*excel) led
to
a reply from that added to my With Sheets(srcName)
section
and it works:

With .Parent.VBProject.VBComponents(.CodeName)
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
End With

Thought you'd like to know, and thanks for leading the way.

--
David

2 Copy Sheet ?'s
 

Tom Ogilvy wrote

It is good that you have figured out how to seach google.

Actually, I do quite often, particularly before posting a question, but I
guess I didn't use the right search words to get what I needed the first
time around, or (more likely) I just got lazy this time <g

--
David

2 Copy Sheet ?'s
 

Bob Phillips wrote

To change the sheet code name use

With Sheets(srcName)
.Parent.VBProject.VBComponents(.CodeName) _
.Properties("_CodeName") = "abc"
End With

Found this tucked in one of my workbooks I forgot about:

ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).Name = tgtName

An alternative, anyway

--
David

2 Copy Sheet ?'s
 

Note in Excel 97, that will raise an error (illegal operation) if the VBE
has not been previously opened. That may not be a consideration for you,
but it is not a general solution.

--
Regards,
Tom Ogilvy

David Turner wrote in message
...
Bob Phillips wrote

To change the sheet code name use

With Sheets(srcName)
.Parent.VBProject.VBComponents(.CodeName) _
.Properties("_CodeName") = "abc"
End With

Found this tucked in one of my workbooks I forgot about:

ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).Name = tgtName

An alternative, anyway

--
David

2 Copy Sheet ?'s
 

Curious. Thanks for the heads up.

--
David

Tom Ogilvy wrote

Note in Excel 97, that will raise an error (illegal operation) if the VBE
has not been previously opened. That may not be a consideration for you,
but it is not a general solution.

--
Regards,
Tom Ogilvy

David Turner wrote in message
...
Bob Phillips wrote

To change the sheet code name use

With Sheets(srcName)
.Parent.VBProject.VBComponents(.CodeName) _
.Properties("_CodeName") = "abc"
End With

Found this tucked in one of my workbooks I forgot about:

ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).Name = tgtName

An alternative, anyway

--
David