Macro runs on wrong workbook on close

Hi all,

I have a macro on a particular workbook that runs when the workbook closes.

This runs fine when I close the workbook itself. If I happen to have other workbooks open and then I close EXCEL rather than an individual workbook, my macro that runs on close always runs when it is not the active workbook.

To put it another way, say that my 'run on close' macro is workbook 1. I also have workbooks 2 and 3 open as well. If workbook 3 is the active workbook and I choose to close Excel (and thus close all three workbooks), my 'run on close' macro runs straight away and, worse still, will run it on workbook 3 (my active workbook).

How can I stop the macro running until it is the active workbook in the closing chain, so that it only runs on the correct workbook? (workbook 1 in my example)

Thanks for any help.

Your macro must not be specifying which workbook to operate on, so Excel is defaulting to using the ActiveWorkbook.
If you paste the code of your BeforeClose macro changes can be adjusted to make sure it runs where it is supposed to
If you are happy to edit the code yourself you need to make sure everything is reference to 'ThisWorkbook'.

You are correct, that is the drawback of coding it that way.
As I said before, if you post your 'BeforeClose' code then we can offer advice on how to alter it to make it work on the correct workbook.

ok, sorry, I should have been clearer, it is the code that runs on the wrong workbook that we need to see, so in this case I would need to see the 'Sort' macro.

Try the following instead.

Note that at the beginning I create a reference to to the worksheet in ThisWorkbook. The object 'ThisWorkbook' always represents the workbook that the macro is stored in.
I have also done away with a lot of the select statements as they are unneccasary. You do not need to select a range, and then refer to the selection, you can just refer directly to the range.
For example;

Please Login or Register  to view this content.

becomes:

Please Login or Register  to view this content.

Here is the code:

Please Login or Register  to view this content.

Incidently I have highlighted the sort lines as I am unsure what you are trying to achieve there?
At the moment it sorts into Ascending order by B4, then it takes that data and resorts it again but this time into Ascending order by D4, as in two SEPERATE sorts where the second one will destroy the results of the first one.
Were you actually wanting to sort by B4, and then have those sorted results sorted by D4 as a secondary sort?