First, I think this kind of thing should be done when the workbook opens--not
before it closes.

If your code makes a change, then the user will be prompted to save or discard
the changes.

If they discard the changes, then your work is lost. And there could be valid
reasons why the user wants to discard the changes -- maybe they deleted 87
sheets in error!

That's one of the reasons I don't think the code should decide, too. I don't
think any developer knows what the user intends to do with the changed workbook.

In stead, I'd use the workbook_open event.

And you could call a procedure in a worksheet module with code that uses the
Codename of the sheet:

Call Sheet4.SortYearMonthDayAscending

or code that uses the name of the sheet on the tab (visible in excel):

Call Worksheets("Sales Data for Our Dept").SortYearMonthDayAscending

====
Make sure you make the SortYearMonthDayAscending procedure public, not private:

Public Sub SortYearMonthDayAscending

===========
All that said, I think Chip's suggestion to put the code into a General module
makes the most sense.

On 05/16/2010 23:46, Philosophaie wrote:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call SortYearMonthDayAscending
End Sub