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

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

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

Hi Ty,