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

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

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/

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/

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(xl up))
End With

precede the Range and Cells with the period/fullstop

--
Regards,
Tom Ogilvy


Shetty wrote in message
om...
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/

Thats the first time I have seen an explanation of Xlup that was
understandable. Many thanks

Diverdon


"Tom Ogilvy" wrote in message
...
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(xl up))
End With

precede the Range and Cells with the period/fullstop

--
Regards,
Tom Ogilvy


Shetty wrote in message
om...
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/

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


"Tom Ogilvy" wrote in message ...
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(xl up))
End With

precede the Range and Cells with the period/fullstop

--
Regards,
Tom Ogilvy


Shetty wrote in message
om...
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/