Sorry about that. I should have been more specific, yes the combobox is on a
sheet.
The sheet is called "REGRESSION" and the values that I want to copy are on a
separate worksheet called "HCDATA" which can be viewed in the code. So
essentially what I want to do is when I change the combobox I want it to
retrieve (copy) data from worksheet "HCDATA" and then paste it to the same
sheet that the combobox is on "REGRESSION".

"crazybass2" wrote:

OK...looks like you are using a ComboBox on a sheet. I assumed you were
using a Userform-Combobox. Which sheet is the combobox on?

I'm headed to lunch now, but I'll see what I can do when I get back.

Mike

"Steve" wrote:

Nope, I still receive the same error:
Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

When use debug highlights:
Range(Sheets("HCDATA").Cells(2, 1), Sheets("HCDATA").Cells(84,
1)).Copy_ Destination:=Worksheets("REGRESSION").Range(Cells( 9, 2),
Cells(91, 2))


"crazybass2" wrote:

Your sheet reference is in the wrong place. When you are refering to a sheet
from a userform or another sheet you must put the sheet reference with the
lowest level, in this case the Cells property not the Range property.

Your modified code is below:

If cmbSELECT.Value = "HC" Then
Range(Sheets("HCDATA").Cells(2, 1), Sheets("HCDATA").Cells(84, 1)).Copy _
Destination:=Worksheets("REGRESSION").Range(Cells( 9, 2), Cells(91, 2))
ElseIf cmbSELECT.Value = "LC" Then
Range(Sheets("LCDATA").Cells(2, 1), Sheets("LCDATA").Cells(84, 1)).Copy _
Destination:=Worksheets("REGRESSION").Range(Cells( 9, 2), Cells(91, 2))
End If


"Steve" wrote:

I receive an error code of '1004' when I use the following code:

Public Sub cmbSelect_Click()
If cmbSelect.Value = "HC" Then
Worksheets("HCDATA").Range(Cells(2, 1), Cells(84, 1)).Copy
Destination:=Worksheets("REGRESSION").Range(Cells( 9, 2), Cells(91, 2))
ElseIf cmbSelect.Value = "LC" Then
Worksheets("LCDATA").Range(Cells(2, 1), Cells(84, 1)).Copy
Destination:=Worksheets("REGRESSION").Range(Cells( 9, 2), Cells(91, 2))
End If
End Sub

I believe its caused by the fact that I cannot get focus on the sheet I want
to copy from when using the combobox. Is there any way around this?