I would suggest using datafilterautofilterfilter on your valuecopy the
bunch at once but
something like this without selections or screen updating needed. UNTESTED

for i 100 to 2 step-1
with sheets("sheet1")
str = .Range("C25")
dlr=sheets("dest").cells(rows.count,"a").end(xlup) .row
if .cells(i,"a")=strval then sheets("dest").rows(dlr).value=.rows(i).value
next i
end with


--
Don Guillett
SalesAid Software

"Gert-Jan" wrote in message
...
Hi, this macro is supposed to copy all the rows with a specific value (in
C25) to another sheet. But, only the first row will be copied. Can someone
help?? Or have a better suggestion??

Sub Copy()
Application.ScreenUpdating = False
With Sheets("Sheet1")
Dim i As Long, sTargetValue As String
sTargetValue = Sheets("Sheet1").Range("C25")
For i = 100 To 1 Step -1
If Cells(i, "A").Text = sTargetValue Then
Rows(i).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
End If
Next i
End With
End Sub