Microsoft Excel - How to make a macro delete itself after it is executed

Asked By Ty on 04-Aug-11 09:47 AM
Hi all,

I have created a template workbook that is, for example, named testworkbook-template.XLT.  I have a macro that automatically runs when it is opened which generates the next sequential number to a specified cell, then saves the file as testworkbook-template.XLT (replaces the file).  Then the macro saves the file using the newly generated next number from that cell in the file name, replacing the word template with the number. For example, if the number in the cell is 1001, when I open the template, the macro changes the cell to 1002, saves the file as testworkbook-template.XLT, then saves it again as testworkbook-1002.XLS.  The next time I open testworkbook-template.XLT, it does exactly what I want and runs the macro, changing the cell to 1003, updates the saved template file, and saves a new document named testworkbook-1003.XLS.

The problem comes when I re-open any of the numbered files, such as testworkbook-1002.XLS and it automatically runs the macro again and changes both the template and that file.  How do I make it so that the macro is deleted, removed, or 'turned off' on the numbered files so that no changes are made when these are re-opened, but the macro stays in place and keeps functioning in the testworkbook-template.XLT file?

I am using excel 2003, and the existing macro was created in Microsoft Visual Basic.  The macro is located in ThisWorkbook, and looks like this (PS, I know absolutely nothing about Visual Basic, so please excuse if this is remedial and please make your responses very simple, Thanks!):

Sub Workbook_Open()
Range("C3").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]+1"
    Range("C3").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
    Range("B3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "1"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=RC[1]"
    Range("B3").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
    Range("C3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
      ("C:\Documents and Settings\utgtfoose\Desktop\New Part Form Template.XLT"), FileFormat:= _
      xlTemplate, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
      False, CreateBackup:=False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
      ("C:\Documents and Settings\utgtfoose\Desktop\New Part Request-" & Range("C3") & ".XLS"), FileFormat:= _
      xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
      False, CreateBackup:=False
End Sub
Riley K replied to Ty on 04-Aug-11 11:49 AM

If you only want to use the macro one time then
Place your macro in a module called Module1 together with the macro
deletemodule and run the sub deletemodule.

The whole Module will be deleted, remember that.


Sub deletemodule()
Yourmacro
With ThisWorkbook.VBProject.VBComponents
..Remove .Item("Module1")
End With
End Sub
 
Sub Yourmacro()
MsgBox "Hi"
End Sub

Also check this link

Pichart Y. replied to Ty on 04-Aug-11 11:54 AM
Hi Ty,

I have design, new workbook with the same task for you...
After you click the button, then the file will 

1) I have change your file to be Auto_Open, this means that immediately you open the file, then it will auto run and after finish, automatically close the application.
2) you will find your new excel.xls in your path and the file.xlt will update the cell C3 and also saved.
3) I add another 1 line to check, if activeworkbook ="New Part Form Template.xlt" then perform task, else nothing.

Remark:
1) For this is Auto run, then to interrupt, press Shift during open the file, till the file open.
2) Please change the path to be yours.
2) Change the number in sheet 1 cell C3 to be your last number of file, then the next file will be Last num + 1


Sample file ---> New Part Form Template.zip
----------------------------------------------------------------------
Sub auto_Open()

FileChk = ActiveWorkbook.Name
If FileChk = "New Part Form Template.xlt" Then

Range("C3").Value = Range("C3").Value + 1
NewNm = Range("C3").Value

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
      ("G:\_EggHeadCafe\New Part Form Template.XLT"), FileFormat:= _
      xlTemplate, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
      False, CreateBackup:=False
      

    ActiveWorkbook.SaveAs Filename:= _
      ("G:\_EggHeadCafe\New Part Request-" & NewNm & ".XLS"), FileFormat:= _
      xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
      False, CreateBackup:=False
      
End If
Application.Quit

End Sub
-------------------------------------------------------------------------------------------

Pichart Y.