Bob is right, put your macro in ThisWorkbook. I did this this for a recent
project, to delete any pivot tables that users created and I called another
macro, to make all sheets, but one, hidden, before the workbook closed. Here
is the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.TableRange2.Clear
Next pt
Next ws

Call HideSheets
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Bob Umlas" wrote:

In the VBE, double-click the ThisWorkbook in the Project window, enter this
code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
update 'runs your macro
End Sub

Bob Umlas
Excel MVP

"Al" wrote in message
...
Hello
I would like to run this code before the file is closed.

Sub Update()

Msg = "Update File?"
Title = "File Update"
Response = MsgBox(Msg, vbYesNoCancel + vbQuestion, Title)
If Response = vbNo Then
Exit Sub
End If

If Response = vbCancel Then
Exit Sub
End If

Run file update code

MsgBox "File Updated"


End Sub

How do I do this?
Thanks