WL

I think you want something like this

Dim Rng As Range
Dim Cell as Range

Set Rng = Sheet1.Range("a1",Sheet1.Range("A65536").End(xlUp) )

For Each Cell In Rng.Cells
If Cell.Value = "I am stupid" Then
Cell.Copy Destination:=Sheet2.Range("A65536").End(xlUp).Offs et(1,0)
End If
Next Cell


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"WiReLaD" wrote in message
...
Hello,
What's the best way to do this.

I've got some data on sheet1 that I want to copy to sheet2 on a click of a
button.
The data is in say A1:A4 cells. (Sometimes) It could be more or less. I
need
it to scan
through these cells and copy the data into cells on sheet2.

Ideally, Im trying to say!
If sheet1.range = "I am stupid" then
if sheet1.range "A1" "" then
range = range.copy sheet2. range ("A1")
end if
end if

I want it to scan through this range until it reaches nothing cell ""

Kinda - Do until range.cell = ""

any idea's please!

WiReLaD