Do you have a module named Auto_Open? If you do, you shouldn't.

I believe papou assumed you had a separate module (don't have procedures and
modules with the same name) that contained only the Auto_Open procedure.

--
Regards,
Tom Ogilvy


"Alan B" wrote:

Pascal,

Thank you for your response. I have created a second Macro called sub
delandsave() which contains the following code:

Sub delandsave()
'
'Deletes auto_open
'
Dim MyModule
Set MyModule = ThisWorkbook.VBProject.VBComponents("Auto_Open")
ThisWorkbook.VBProject.VBComponents.Remove MyModule
'
'Creates new workbook using date in DATA worksheet cell AG1 as name
'
Dim SaveName As String
SaveName = Sheets("query").Range("ag1").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="\\colorado\Impromptu\finance\Stock Sheets\"
& SaveName & ".xls"

End Sub

This is called from the auto_open, and should delete the auto_open macro.
When I run it, I keep getting an error on the "Set MyModule =.." line.

Any ideas?

Regards

ALan Berry

"papou" wrote:

Alan
You may also need to amend security settings:
From the Excel spreadsheet menu:
Tools, Macro, Security
Approved Editors tab (please note caption may vary, I have a french version
of Excel)
Tick "Trust Visual Basic Project"
Click OK and save your workbook

HTH
Cordially
Pascal

"papou" a écrit dans le message de news:
...
Hello Alan

Place the code into your auto_open macro below your last instructions and
amend accordingly (Module name):

Dim MyModule
Set MyModule = ThisWorkbook.VBProject.VBComponents("Module2")
ThisWorkbook.VBProject.VBComponents.Remove MyModule

HTH
Cordially
Pascal



"Alan B" <Alan a écrit dans le message de
news: ...
I have an auto_open macro on a workbook that performs some functions, then
does a save as with today's date as the file name. The problem is that
the
new file has the auto_open macro in it too, and when I open it, the macro
runs. I would like to put some code in to prevent the macros being saved
with the new sheet, or in some way delete the auto-open before it saves.
Any
ideas would be welcome.

Regards

Alan