The obvious answer is you no longer have a sheet named Sheet1. Your note
says refresh the data on Sheet2, so I susptect that is the case. Change the
name to sheet2
Private Sub Workbook_Open()
Dim numRows As Long

'Refresh data of Sheet2

Workbooks.Open Filename:="C:\CCF\Contracts1.xls"
With ThisWorkbook.Worksheets("Sheet2")

'Determine number of rows
numRows = Application.CountA(ActiveSheet.Range("A:A"))

ActiveSheet.Range("A1:AI" & numRows).Copy _
.Range("Contracts!A1")
End With
ActiveWorkbook.Close SaveChanges:=False
' this may be your next error - you probably need to qualify this
cmbContracts.ListFillRange = "Contracts!A2:C" & numRows
End Sub

--
Regards,
Tom Ogilvy

Squid wrote in message
...
The follow code worked when I had it in a
CommandButton_Click Event (used that event for testing
purposes). But when I transferred the code to the
Workbook_Open Event, I receive a "Run-Time error '9':
Subscript out of range" at "With ThisWorkbook... " Why??

TIA
Mike

Private Sub Workbook_Open()
Dim numRows As Long

'Refresh data of Sheet2

Workbooks.Open Filename:="C:\CCF\Contracts1.xls"
With ThisWorkbook.Worksheets("Sheet1")

'Determine number of rows
numRows = Application.CountA(ActiveSheet.Range
("A:A"))

ActiveSheet.Range("A1:AI" & numRows).Copy .Range
("Contracts!A1")


End With
ActiveWorkbook.Close

cmbContracts.ListFillRange = "Contracts!A2:C" &
numRows

End Sub