Hi

I am getting this error 1004 "select method of range class failed" whe
my macro tries selecting some cells in a sheet,

can someonw help about the possible causes!!

--
Message posted from http://www.ExcelForum.com

Hi paritoshmehta ,

It's hard to say without seeing the code. But most likely, the active
worksheet is not the one on which you're trying to select cells. That will
cause a runtime error.

To solve it, you have a few options:

1) Don't select cells. Most (almost all) actions can be performed without
changing the current selection. For example, instead of doing this:

Sheets("Sheet1").Select
Range("A1").Select
MsgBox ActiveCell.Value

you could do this:

MsgBox Sheets("Sheet1").Range("A1").Value

Besides being faster, the second method doesn't change the user's active
selection.

2) If you must select cells, try the GoTo method instead:

Application.GoTo Sheets("Sheet1").Range("C1:D10")

This will not fail if the active worksheet is not Sheet1.


It's also possible that you would get this error if you are running code
from a CommandButton placed on a worksheet - if that's the case, try setting
the TakeFocusOnClick property of the button to False.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Hi

I am getting this error 1004 "select method of range class failed"
when my macro tries selecting some cells in a sheet,

can someonw help about the possible causes!!!


---
Message posted from http://www.ExcelForum.com/

this is the code:

Sheets("Quality Monitoring").Select
Range("a2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("a2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False

I tried the 2nd method you specified:
Application.GoTo Sheets("Quality Monitoring").Range("a2:C2")
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("a2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False

but got another error 1004 "Method range of object_Worksheet failed" o
the following line:
Range(Selection, Selection.End(xlDown)).Select

Please help!

--
Message posted from http://www.ExcelForum.com

Hi,

OK, you may be running into a separate issue here, which is not
fully-qualifying your Range references. Typically, using Range or Cells
without specifying the worksheet will refer to a range on the active
worksheet. However, if your code is running from a worksheet class module,
this is not the case. Using unqualified range references in a worksheet
class module will give you a reference to a range on the worksheet in which
your code is placed.

Try this code instead to see how it works:

With Sheets("Quality Monitoring")
.Range("a2:C2").Select
.Range(.Range("A2:C2"), .Range("A2:C2").End(xlDown)).Copy
.Range("A2").PasteSpecial Paste:=xlValues
End With
Application.CutCopyMode = False

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


this is the code:

Sheets("Quality Monitoring").Select
Range("a2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("a2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

I tried the 2nd method you specified:
Application.GoTo Sheets("Quality Monitoring").Range("a2:C2")
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("a2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

but got another error 1004 "Method range of object_Worksheet failed"
on the following line:
Range(Selection, Selection.End(xlDown)).Select

Please help!!


---
Message posted from http://www.ExcelForum.com/