2 Copy Sheet ?'s

  • Thread starter Thread starter David Turner
  • Start date Start date
D

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
 
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)
 
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
Click to expand...

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
Click to expand...

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

Thanks
 
Bob Phillips wrote
To delete code, visit Chip Pearson';s site for details
http://www.cpearson.com/excel/vbe.htm
Click to expand...

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.
 
Haven't checked that page out in a while, but I thought it had details on
deleting code. Thanks for updating us.

Bob
 
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 wrote
It is good that you have figured out how to seach google.
Click to expand...

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>
 
Bob Phillips wrote
To change the sheet code name use

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

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

ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).Name = tgtName

An alternative, anyway
 
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.
 
Back
Top