Workbook_Open Event Runs Too Soon

[Billy Hill]

I get a daily report emailed to me in Excel format. It's hidious to read so I made a macro to clean it up so I can run it when I opent he workbook.

There is only one sheet, named LockedSupplierReport, and I'd like to use the Workbook_Open event to test for this sheet name and run the macro automagically when it opens.

If I run the two lines in the debug window while the report is opened it works. But when I put the code in ThisWorkbook in the Workbook_Open function it seems to run before the report opens.

Private Sub Workbook_Open()
s = Sheets(1).Name
If s = "LockedSupplierReport" Then Call LockedSupplierReport
End Sub

To test this I changed the code to this, saved and closed excel, then re-opened the report.

Private Sub Workbook_Open()
s = Sheets(1).Name
MsgBox s
If s = "LockedSupplierReport" Then Call LockedSupplierReport
MsgBox "Didn't work, did it?"
End Sub

When I open it, Excel opens and pops the msg box up with "Sheet1" then "Didn't work", then it opens the report.

How can I get this code to run after the report is open?

 

[Billy Hill]

Thanks Rory. I did a little research and, understandably so, all that selecting is slowing down my spreadsheet.

Thanks for the heads up.