Error when Opening workbbok

[mayoung]

I run this code two different ways. First way is by clicking on the workbook icon itself and when the workbook opens the code excutes perfectly. I also have a another workbook with a form button that when pressed opens this workbook and this code runs. When the workbook opens the code errors in the module at this line: Sheets("COS Attach").Select. Any Suggestions on how to fix? Thank

This code is in ThisWorkbook:

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    'Module102
    Application.Run "Module102.WorkBookOpen"
    Application.ScreenUpdating = True
End Sub

This code is in the module:

Private Sub WorkbookOpen()
    Dim j As Integer
    Dim i As Integer
    Dim SmallScroll As Long
    Dim ToLeft As Long
  ' Application.Wait (Now + TimeValue("0:00:05"))
    Sheets("COS Attach").Select
    Range("A7").Select
    Application.GoTo reference:=ActiveCell, Scroll:=True
    With ActiveWindow
        i = .VisibleRange.Rows.Count / 2
        j = .VisibleRange.Columns.Count / 2
        .SmallScroll Up:=i, ToLeft:=j
    End With
    ActiveWindow.SmallScroll Down:=-20
End Sub

 

[mayoung]

Problem Solved:

Found my answer in a MR Excel post dated Apr 4th, 2004 by Dmcoffman Titled: Runtime error '9':Subscript out of range


Per dmcoffman, This is the scenerio I encountered.


As I pointed out the code worked when a single worksheet was opened. The problem was only evident when multiple worksheets were opened.


Don's belief is that Excel is opening all the workbooks before executing any code, thus it was attempting to select a sheet not available in the workbook that was active. Based on this I changed this code:

Sheets(COS Attach).Select
Range("A7").Select

-to this-

Workbooks("COS Sheets 2015.xlsm").Worksheets("COS Attach").Activate
    Range("A7").Select

If you change the .Activate to .Select you will get the time error, must end in .Activate.


Good Job!!