Workbook_Open Event

[Squid]

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

 

[Frank Kabel]

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

Frank

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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")

 

[PokerDude]

Ok, eventually I will get the code right. I think I am closer than ever
before with your help (if you are ever in Pittsburgh... I owe you a beer).
Thanks again.. Mike

The problem now is it is copying the contents of the wrong worksheet. It
copies data from workbook Settlement.xls, sheet Settlement (first sheet in
workbook), instead of workbook Contracts1.xls, sheet Sheet1. Plus it doesnt
like my code for the combobox. What am I doing wrong again?

The code you gave me earlier this week worked perfect with a command button.
But putting it in the Workbook_Open event it doesnt want to work correctly.
What is the reason for this?

Private Sub Workbook_Open()
Dim numRows As Long

'Refresh data of Contracts worksheet in C:\CCF\Settlement4.xls from data
contained
'in worksheet Sheet1 in C:\CCF\Contracts1.xls

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

'Determine number of rows in C:\CCF\Contracts1.xls Sheet1

numRows = Application.CountA(ActiveSheet.Range("A:A"))

'Copy from C:\CCF\Contracts1.xls!Sheet1 and
'Paste in C:\CCF\Settlement4.xls!Contracts
ActiveSheet.Range("A1:AI" & numRows).Copy .Range("A1")


'modify combobox properties to update listfillrange
cmbContracts.ListFillRange = "Contracts!A2:C" & numRows


End With
ActiveWorkbook.Close

'Select Settlement worksheet so this is what the user sees
Worksheets("Settlement").Select

End Sub

 

[Tom Ogilvy]

Private Sub Workbook_Open()
Dim numRows As Long
Dim sh1 as Worksheet
Dim sh2 as Worksheet
'Refresh data of Contracts worksheet in C:\CCF\Settlement4.xls from data
contained
'in worksheet Sheet1 in C:\CCF\Contracts1.xls

Workbooks.Open Filename:="C:\CCF\Contracts1.xls"
set sh1 = Workbooks("Contracts1.xls").Worksheets("Sheet1")
set sh2 = Workbooks("Settlement4.xls").Worksheets("Contracts")
'Determine number of rows in C:\CCF\Contracts1.xls Sheet1

numRows = Application.CountA(sh1.Range("A:A"))

'Copy from C:\CCF\Contracts1.xls!Sheet1 and
'Paste in C:\CCF\Settlement4.xls!Contracts
sh1.Range("A1:AI" & numRows).Copy sh2.Range("A1")


'modify combobox properties to update listfillrange
sh2.Parent.Worksheets("Settlement").cmbContracts. _
ListFillRange = sh2.Range("A2"). _
Resize(numRows).Address(External:=True)


End With
ActiveWorkbook.Close

'Select Settlement worksheet so this is what the user sees
Worksheets("Settlement").Select

End Sub

You don't say where cmbContract is located, so I am guessing it is in
Settlement4.xls on sheet Settlement.

--
Regards,
Tom Ogilvy



news

 

[Squid]

Tom, Thanks again for all your help. If you are ever in Pittsburgh, I think
I owe you a beer or two!

 

[Squid]

Tom, Thanks again for all your help. If you are ever in Pittsburgh, I think
I owe you a beer or two!