Workbook_Open Event
I had a second look at your code and I notices you do
With With ThisWorkbook.Worksheets("Sheet2")
..
..
..
copysomething to .Range("Contracts!A1")
If contracts is the sheet where you want the data copied to you should use
With ThisWorkbook.Worksheets("Contracts")
..
..
..
copysomething to .Range("A1")
--
Regards,
Tom Ogilvy
Tom Ogilvy wrote in message
...
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
|