Hi
try the following: change the line
With ThisWorkbook.Worksheets("Sheet1")
to
With ActiveWorkbook.Worksheets("Sheet1")

Frank


Squid wrote:
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