Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Excel ComboBox / Copy Error
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,814
Default 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 QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 167
Default 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 QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,814
Default 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 QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 167
Default 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 QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,814
Default 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 QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 167
Default 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 QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,814
Default Excel ComboBox / Copy Error
Works Great!
Thanks!
Steve

"crazybass2" wrote:

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 QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 167
Default Excel ComboBox / Copy Error
Steve,

Reminder, as a courtesy, when your question is answered, you should let the
group know. When viewing the threads the bottom of each thread should have a
line that says "Did this post answer the question?" with a Yes/No button.
Clicking Yes when your question is answered gives the person who helped you
positive feedback.

Mike
"Steve" wrote:

Works Great!
Thanks!
Steve

"crazybass2" wrote:

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 QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy with ComboBox Jari Excel Programming 0 December 4th 05 09:46 AM
Error 13 with combobox Linebaker[_2_] Excel Programming 2 August 11th 05 01:34 AM
Combobox creates error Stephen Excel Worksheet Functions 0 January 23rd 05 10:55 AM
error (ComboBox-?) Alex Excel Programming 2 June 18th 04 02:52 AM
Excel - Copy range of cells based on Combobox Selection Excel-erate2004 Excel Programming 2 April 3rd 04 05:35 PM


All times are GMT +1. The time now is 04:24 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement