Hi Tom,
Sorry to jump in between. Just to understand some logic.
1. set rng = Range(Cells(1,"K"),Cells(rows.count,"K").End(xlup) )
Pl explain what it does and how it works.
2. When you have already used a loop (for each cell in rng) why do you
need
to use rw = rw + 1 ? Moreover you set rw=2. Why not rw=1?

Pl bear with me and explain it so that I can also use such compect
statements instead of 5-6 statements and avoid select method.

Regards,
Shetty


"Tom Ogilvy" wrote in message ...
Dim rng as Range, cell as Cell
Dim rw as Long
rw = 2
set rng = Range(Cells(1,"K"),Cells(rows.count,"K").End(xlup) )
for each cell in rng
if cell.Value = 6 then
cell.Entirerow.copy Destination:=worksheets("Sheet2") _
.Cells(rw,1)
rw = rw + 1
end if
Next

another approach would be to click in you data, do Data=Filter=Autofilter
go to column k and in the dropdown select your values.

Use the row borders to select all the data, then do Edit=Copy

go to A1 on the new sheet and do Edit=Paste

This will copy just the visible rows (which should be the rows that contain
your value).

Go back to the original sheet and do Data=Filter=Autofilter to remove the
autofilter.

If you want a macro, turn on the macro recorder while you do this manually.

--
Regards,
Tom Ogilvy



"burlybo" wrote in message
...
I have been looking everywhere for code to simply copy the rows from one
sheet that have a certain value in column K to a new sheet. I can't
find the code and I'm fairly new to this stuff.

I found out how to turn the cells with the value I'm looking for red,
but I couldn't get the rows to copy into a new sheet.

Is this easy? It seems to me it should be easy, right? Should I use a
select statement?


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