Clear macro before save

[Jon]

I have a macro that runs when the file is opened (via Workbook Ope
event).

At the end of the macro, the file is saved-as to another location.

The problem is; because the macro is part of the Open event, it i
saved in the new file too. When the new file is opened, it tries to ru
this now-unnecessary macro (and fails).


What I'd like to be able to do is to clear out the macro in the ne
file.

Surely there's an easy way to do this?


Help

 

[Jon]

Anybody? :confused

 

[Frank Kabel]

Hi
see
http://www.cpearson.com/excel/vbe.htm

 

[Jon]

Thanks Frank.

To be honest; it's a bit beyond me. Also; I'm calling the "delete" bi
at the end of Workbook.Open which that guide says I can't do....

I have this in Masterfile.xls

Private Sub Workbook_Open()

' Macro bit in here (copy paste special values for most part
' then

ActiveWorkbook.SaveAs FileName:= _
"ChildFile.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

end sub


What is then happening is that the destination file (myfilename.xls
when it opens up tries to run the script again. I'm not sure wher
abouts I should issue a call to delete part of the macro. Do I save
then call the macro, then re-save

 

[Jon]

Still stuck

 

[AlfD]

Hi!

Without getting too deep in VBA, you could put a line in the code afte
your copy/paste bit which puts a value into a spare cell in th
workbook. e.g.

worksheets("LastOne").range("ZZ1")="MyCode"

Then put a line in the code before the save-as routine which checks t
see if the cell has been filled.

If worksheets("LastOne").Range("ZZ1")<>"MyCode" then

{do the save-as}

else exit sub

end if

The saved version will have the filled cell.

If you now want your master to have the capability to clone itsel
again, then empty ZZ1

worksheets("LastOne").Range("ZZ1")=""


Al

 

[Jon]

Doh! So obvious when you put it that way!

I simply make the save-as version different, and make both files check
a cell (as you've suggested)

thanks mate