+ Reply to Thread
Results 1 to 10 of 10

Excel ComboBox / Copy Error

Hybrid View

  1. 07-05-2006, 10:45 AM #1
    Steve
    Guest

    Excel ComboBox / Copy Error

    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?

    Reply With QuoteRegister To Reply
  2. 07-05-2006, 01:25 PM #2
    crazybass2
    Guest

    RE: Excel ComboBox / Copy Error

    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?
    >
    Reply With QuoteRegister To Reply
  3. 07-05-2006, 02:00 PM #3
    Steve
    Guest

    RE: Excel ComboBox / Copy Error

    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?
    > >
    Reply With QuoteRegister To Reply
  4. 07-05-2006, 02:25 PM #4
    crazybass2
    Guest

    RE: Excel ComboBox / Copy Error

    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?
    > > >
    Reply With QuoteRegister To Reply
  5. 07-05-2006, 02:35 PM #5
    Steve
    Guest

    RE: Excel ComboBox / Copy Error

    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?
    > > > >
    Reply With QuoteRegister To Reply
  6. 07-05-2006, 03:10 PM #6
    crazybass2
    Guest

    RE: Excel ComboBox / Copy Error

    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?
    > > > > >
    Reply With QuoteRegister To Reply
  7. 07-05-2006, 01:15 PM #7
    crazybass2
    Guest

    RE: Excel ComboBox / Copy Error

    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?
    >
    Reply With QuoteRegister To Reply
  8. 07-05-2006, 01:21 PM #8
    crazybass2
    Guest

    RE: Excel ComboBox / Copy Error

    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?
    >
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1