Excel AutoClose macro not running

[JPJeffery]

Hello

This macro should save the contents of the first four columns of the 'Monitor Types' worksheet to a .csv file as the spreadsheet closes.

But it's not. The code works when I step through with F8 so it's not a syntax issue as such.

What have I got wrong?

Sub AutoClose()
'
' SaveMonitorsList Macro
' Macro recorded 02/01/2007 by Jeff Jeffery
'

'
    Application.ScreenUpdating = False
    MsgBox ("Saving list of monitors")
    Sheets("Monitor Types").Select
    Columns("A:D").Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="\\bldgb005mcl30\programs\London Local\Inventory\MonitorTypes.csv" _
        , FileFormat:=xlCSV, CreateBackup:=False
    ActiveWindow.Close
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

JJ
[small][purple]Variables won't. Constants aren't[/purple][/small]

 

[combo]

What happens after renaming macro to 'Auto_Close'?

combo

 

[JPJeffery]

No change.

JJ
[small][purple]Variables won't. Constants aren't[/purple][/small]

 

[xlbo]

really? Auto_Close() is the corect name for the macro to run on closing the workbook - AutoClose means nothing to excel....

Where is this code? in the ThisWorkbook module or in a standard module ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

For me all works (after renaming macro to Auto_Close). Can you see the MsgBox window? If not:
- are the macros enabled?
- is the code in standard module?

combo

 

[JPJeffery]

Well, I tell ya, it didn't work after I renamed the macro but now, after reopening the spreadsheet and closing it again it does work.

Go figure!

To answer the outstanding questions, it's in Module1.

My new query is why the screen updates even though I've set 'Application.ScreenUpdating = False'?

JJ
[small][purple]Variables won't. Constants aren't[/purple][/small]