2 Copy Sheet ?'s

[David Turner]

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

 

[Bob Phillips]

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]

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 Turner]

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 (e-mail address removed) 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.

 

[Bob Phillips]

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

Bob

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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

 

[David Turner]

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 Turner]

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.CodeName).Name = tgtName

An alternative, anyway

 

[Tom Ogilvy]

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.

 

[David Turner]

Curious. Thanks for the heads up.