Steve,

OK...this should work. I set my workbook up just like yours with some
generic data and it had no problem. You need the Sheet reference before the
Range and Cells properties and you need to remove the reference to the
Regression sheet.

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

Mike


"Steve" wrote:

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?