Hi All:

I am trying to create code that when a command button is pressed, it will
open a workbook, copy the data and close the workbook. Then paste the data
in the current workbook and fill a combobox. I am receiving an error with
ActiveSheet.Range(Cells(1, 1), Cells(numRows, 35)) .Copy .Range ("A1").
What am I doing wrong?


Private Sub CommandButton1_Click()
Dim numRows As Long

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

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

'*** The number of columns will always be a static 35 (i.e. A:AI) **
ActiveSheet.Range(Cells(1, 1), Cells(numRows, 35)) .Copy .Range
("A1")
.ComboBox1.ListFillRange = .cells(1,1),.cells(numrows,35)
.ComboBox1.ListIndex = 0
End With
ActiveWorkbook.Close

End Sub

I see two possible reasons:
1. There's a space before .Copy
2. numRows turns out to be 0


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"PokerDude" wrote in message
news:knjUb.177634$sv6.938184@attbi_s52...
Hi All:

I am trying to create code that when a command button is pressed, it will
open a workbook, copy the data and close the workbook. Then paste the
data
in the current workbook and fill a combobox. I am receiving an error with
ActiveSheet.Range(Cells(1, 1), Cells(numRows, 35)) .Copy .Range ("A1").
What am I doing wrong?


Private Sub CommandButton1_Click()
Dim numRows As Long

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

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

'*** The number of columns will always be a static 35 (i.e. A:AI) **
ActiveSheet.Range(Cells(1, 1), Cells(numRows, 35)) .Copy .Range
("A1")
.ComboBox1.ListFillRange = .cells(1,1),.cells(numrows,35)
.ComboBox1.ListIndex = 0
End With
ActiveWorkbook.Close

End Sub