Macro assigned to custom button - keeps changing

  • Thread starter Thread starter hobbit
  • Start date Start date
H

hobbit

Hi

I have a macro which deleteds the headings from a
worksheet, saves the file as a .prn file and emails it to
someone.

Tested macro several times and it works fine so I have
created a custom button on toolbar and assigned the macro
to it.

My problem is that when the macro is run for the first
time the button assignment in the original file is
changed to a macro in the .prn file (but the macro is not
saved in the file so does not exist).

So the next time the button is pressed - Excel cannot
find the macro!

Any ideas what is causing this and how to stop it?

Any help at all would be greatly appreciated.


The macro is as follows(don't know if something in the
macro is causing this or not):

Sub Mail_ActiveSheet()

Dim strdate As String
Dim wb As Workbook
Dim CurrFile As String

Set wb = ActiveWorkbook
CurrFile = ActiveWorkbook.FullName
ActiveWorkbook.Save
strdate = Format(Now, "mm-dd-yy")

Rows("1:1").Select
Selection.Delete Shift:=xlUp

ActiveWorkbook.SaveAs Filename:="C:\CAR\" & strdate
& ".prn", FileFormat:=xlTextPrinter, CreateBackup:=False
ActiveWorkbook.SendMail "(e-mail address removed)", _
"CarLoanFile"


Workbooks.Open CurrFile
wb.Close False


End Sub
 
I think you have two choices. Your best bet may be to put the macro in
another workbook (or add-in), one that you do not rename.

Otherwise you should enhance your macro to save the workbook with its
original name and in the XLS workbook format after the PRN file is emailed.
 
Back
Top