I had the same problem and I found the fix in Microsoft website as follows:

http://office.microsoft.com/en-us/ex...346281033.aspx

Creating an Auto_Open macro
If you record a macro and save it using the name "Auto_Open", the macro will run whenever you open the workbook that contains the macro.

1. If you want to save the macro with a particular workbook, open that workbook first.
2. On the Tools menu, point to Macro, and then click Record New Macro.
3. In the Macro name box, type Auto_Open.
4. In the Store macro in box, specify where to save the macro:
* To save the macro in the current workbook, choose This Workbook.
* To create a new workbook to save the macro in, choose New Workbook.
* To save the macro in a hidden workbook that is automatically loaded when you start Excel, choose Personal Macro Workbook.

Note If you choose Personal Macro Workbook, Excel will create and save the macro in a hidden workbook named Personal.xls (if Personal.xls doesn't already exist). In Windows 2000, Personal.xls is saved in the C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLStart folder so that it will be loaded automatically whenever Excel starts. If you want the macro you record and save in Personal.xls to act on a particular workbook, you must also save that workbook in the XLStart folder so that it will also be opened when Excel starts.
5. Click OK, and then perform the actions you want to record.
6. On the Stop Recording toolbar, click the Stop Recording button.
7. If you chose to save the macro in This Workbook or New Workbook in step 4, make sure to save or move the workbook into one of the XLStart folders.

Recording an Auto_Open macro has a number of limitations:

* Many actions you may want to perform cannot be recorded.
* If the workbook in which you save the Auto_Open macro already contains a VBA procedure in its Open event, the VBA procedure for the Open event will override all actions in the Auto_Open macro.
* An Auto_Open macro is ignored when a workbook is opened programmatically by using the Open method.
* An Auto_Open macro runs before any other workbooks are opened. Therefore, if you record actions you want Excel to perform on the default Book1 workbook or a workbook loaded from the XLStart folder, the Auto_Open macro will fail when you restart Excel because it runs before the default and start-up workbooks are opened.

If you encounter these limitations, instead of recording an Auto_Open macro, you must create a VBA procedure for the Open event as described in the next section of this article.