Disassociate Macros when saving under different name

[Guest]

I have a macro which removes some sheets from a workbook, removes shapes
which run macros and saves the file under a new file name.

The macro runs and appears to do what it should but when I try to open the
newly saved file I see the front sheet but get an error message saying
Excel.exe has generated errors and will be closed by Windows.

I wondered if the solution would be to remove all the macros in This
Workbook (that's the only location) before the file save as process in case
the Auto_Open is causing a glitch. If so, how do I do this?

I'm running Office 2000 on Windows 2000.

I've put the code below to help make it clearer.

I'd appreciate any help with this as I've been stuck for hours. Thanks a
lot in anticipation.

Keith
----------
ActiveWindow.DisplayWorkbookTabs = True
Application.DisplayAlerts = False
Sheets("Menu").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Workings").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Month by Month Analysis").Select
ActiveSheet.Shapes("AutoShape 1").Select
Selection.Cut
Sheets("Branch Analysis").Select
ActiveSheet.Shapes("AutoShape 1").Select
Selection.Cut
Sheets("Signup Report").Select
ActiveSheet.Shapes("AutoShape 1").Select
Selection.Cut
Sheets("Banked Report").Select
ActiveSheet.Shapes("AutoShape 1").Select
Selection.Cut
Sheets("Incentives").Select
ActiveSheet.Shapes("AutoShape 1").Select
Selection.Cut
Sheets("Data").Select
ActiveSheet.Shapes("AutoShape 12").Select
Selection.Cut
Sheets("Month by Month Analysis").Select
Range("A1").Select

ThisWorkbook.SaveAs "c:\#Reports\Reports\Reports " & Format(Date,
"dd-mm-yyyy")
End Sub

 

[Guest]

If you think it maybe be you're auto_open macro causing trouble, put code in
it to exit the sub if certain sheets or shapes are missing.
For instance if sheet1. runs code but is removed with your save macro, on
auto_open check for sheet1 If it is not there, exit sub

 

[Tom Ogilvy]

You can open excel in save mode. Go to the run command under the start menu
and type

excel.exe /s

and then try to open your files.

You can also hold down the shift key while you open the file to disable
macros (or change the security setting so you are prompted to disable
macros).


If you still have problems with the files, then removing the macro is
probably not going to rectify you problem.

 

[Guest]

hi,
have a look at this site. it helped me.
http://www.cpearson.com/excel/vbe.htm
be careful not to delete the macro that is deleted all the
macros.

 

[Guest]

Thanks a lot for the replies.

I've at least now proved that by disabling macros the file still won't open.

If I maually save the file without without removing shapes and sheets it's
fine but
that leaves users able to run macros in the copy version which I don't want.

Any ideas what it could be in the code I posted above that is causing the
saved file to corrupt?

Is there a more reliable way of writing the code?

Thanks again

Keith

 

[Tom Ogilvy]

Possibly put the code in an addin. This separates the code from the
workbook.