Simple copy rows with certain value to new sheet

  • Thread starter Thread starter burlybo
  • Start date Start date
B

burlybo

I have been looking everywhere for code to simply copy the rows from on
sheet that have a certain value in column K to a new sheet. I can'
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
select statement
 
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.
 
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
 
If you want to the very bottom of the sheet in column K and then hit the End
key and the up arrow, the selected cell would then be the last cell in that
column that contains data, so Cells(rows.count,"K").End(xlup) is equivalent
to that.

If I then do Range(start reference, end reference) I can refer to the part
of a column. In this case, I reference the first cell (cells(1,"K") and the
last cell with data Cells(rows.count,"K").End(xlup)

now I loop through this reference

for each cell in rng

rw refers the row I want to work with on the second sheet.

So if I find a value containing 6, I want to write that to the second sheet.
I would write it in row rw.

Worksheets("Sheet2").Cells(rw,1)
now increment rw to refer to the next location where i want to write the
next found value.

I start on row 2 because this appears to be a database type worksheet and I
assume the first row will have the column headers.

not that these unqualified references refer to the active sheet
set rng = Range(Cells(1,"K"),Cells(rows.count,"K").End(xlup))

If I wanted to refer to sheet3 for instance and it is not active I would
need to qualify the references

With Worksheets("Sheet3")
set rng = .Range(.Cells(1,"K"),.Cells(rows.count,"K").End(xlup))
End With

precede the Range and Cells with the period/fullstop
 
Thats the first time I have seen an explanation of Xlup that was
understandable. Many thanks

Diverdon
 
Thanks Tom,
Thank you very much. Now I can be thorough with my code in future. Also now I
understand how to use the xlup as well as total raws count as an argument.
Thanks again.
Regards,
Shetty
 
Back
Top