Sheet Name???

[Guest]

I have a macro that emails the ActiveSheet to people in a certain range on
another sheet. The sheet being emailed has a code to name the sheet the value
of cell A3. The sheet is copied into another workbook before it emails. Is
there a way to have the code to name the sheet deleted and the sheet be named
what it is when copied? I don’t want anyone to be able to accidentally
change the sheet name when they receive it.

The code I use is:

Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Application.CutCopyMode = False
Set wb = ActiveWorkbook
With wb
.SaveAs ActiveSheet.Name
Dim MyArr As Variant
MyArr = ThisWorkbook.Sheets("Email").Range("b16:b31")
.SendMail MyArr, ActiveSheet.Name & " " & "2005" & " " & "-" & " " &
"Offshore P&A Activity Report" & " " & "****CONFIDENTIAL****"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub



Thanks,
Chance

 

[Bob Phillips]

Chance,

This will delete the code in Sheet2 class module

Dim VBComp As Object

Set VBComp = ThisWorkbook.VBProject.vbcomponents("Sheet2").codemodule
VBComp.DeleteLines 1, VBComp.CountOfLines

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks for the help, but I’m not sure where I need to insert the code. The
sheet that is emailed is copied from the previous day. This report is done
daily so the sheet name always changes.

Chance

 

[Bob Phillips]

I would put it in a standard code module in your Personal.xls workbook, and
assign it to a toolbar button.

--

HTH

RP
(remove nothere from the email address if mailing direct)