Copying Range
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 |
Copying Range
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 |
All times are GMT +1. The time now is 01:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com