Run-time error 1004 when using Auto_Open() in Excel

Question

So I've created a simple macro that loops through all worksheets and AutoFits all columns. It works when I run it manually but I want it to run each time I launch Excel automatically. I placed it in a module and named the sub Auto_Open(). The problem is that I'm getting

"Run-time error '1004': Method 'Worksheets' of object '_Global' failed."

each time I start up Excel now.

Here's the code. The debugger says its the For Each portion that is causing the problem but I don't understand why. What am I missing? Thanks for any help.

Sub AUTO_OPEN()
'
' AutoFit_All_Columns Macro
'

Dim ws As Worksheet

For Each ws In Worksheets
    ws.UsedRange.Columns.AutoFit
Next ws

End Sub

Answer 1

For Each ws In ThisWorkbook.Worksheets

Answer 2

I think you need to use ActiveWorkbook. Try this code :

Sub AUTO_OPEN()
    '
    ' AutoFit_All_Columns Macro
    '

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        ws.UsedRange.Columns.AutoFit
    Next ws

End Sub

Answer 3

I found an alternative using the Workbook_Open method that seems to work just fine. Placing this in the "ThisWorkbook" portion of the built-in Personal VBAProject will run AutoFit on all columns automatically when any workbook is opened.

Option Explicit
Private WithEvents App As Application

Private Sub Workbook_Open()
    Set App = Application
End Sub

Private Sub App_WorkbookOpen(ByVal WB As Workbook)

    Dim ws As Worksheet
    For Each ws In WB.Worksheets
        ws.Columns.AutoFit
    Next ws

End Sub