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/