moving rows based on criteria

  • Thread starter Thread starter Phillips
  • Start date Start date
P

Phillips

I would like to move entire rows based on a criteria in one of it's columns.
I would like to be able to bo this several times a day. I want it so it does
not effect the order of the rest of the rows.

example:
if column 20 is shipped and is a date

If column 20 have a value, then move it to worksheet "SHIPPED"

I would like to have this so that I can run a macro, and have it move all
shipped to the shipped folder.
All BACKORDERED (column 21 and is a logical) if set to TRUE, move to
worksheet BACKORDERED
all (based on a value from a dropdown) that equal "AquaCulture" to worksheet
"Aquaculturist"

Thanks
Phil
 
set an Auto-Filter on your data. Then you can set it for
each of your requirements and copy the results to the
respective sheet.
You don't say if you want to cut the data or copy it. you
also need to be clear if you want the data 'appended' to
data already on the target sheet, or whether the target
sheet should be cleared.

One way to try to code this is to turn on the macro
recorder , set the filter & manully do all three tasks.
stop the recorder then examine the code. The 'macro'
produced, invariably needs quite a bit of adjusting to
make it more versatile, but it will show you the key code.

We're willing to show code, but if you could clarify the
points indicated, that would help.

Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
I would like to move entire rows based on a criteria in one of it's columns.
I would like to be able to bo this several times a day. I want it so it does
not effect the order of the rest of the rows.

example:
if column 20 is shipped and is a date

If column 20 have a value, then move it to worksheet "SHIPPED"

I would like to have this so that I can run a macro, and have it move all
shipped to the shipped folder.
All BACKORDERED (column 21 and is a logical) if set to TRUE, move to
worksheet BACKORDERED
all (based on a value from a dropdown) that
Click to expand...
equal "AquaCulture" to worksheet
 
In the past, when I used auto filter and cut and paste, it moved all rows in
between (even hidden) and I don't want this to happen! I would prefer, not
to mess with filters, as sometimes, I do have some funky and complicated
filters inpalce. But. I can live with that, if I can figure out how to
prevent the hidden rows from being moved. I have read that Excel 2002 is NOT
supposed to do that, but it does! (I do have 2002!), that is why I was
thinking my best bet would be to step through each record and then test for
the conditions, and if the conditions, move it or leave as required

I DO want to append the data to the end of the destination worksheets, and I
DO want to "CUT" the data

TIA,
Phil
 
Hi Phillips,
In the past, when I used auto filter and cut and paste, it moved all rows in
between (even hidden) and I don't want this to happen! I would prefer, not
to mess with filters, as sometimes, I do have some funky and complicated
filters inpalce. But. I can live with that, if I can figure out how to
prevent the hidden rows from being moved.
Click to expand...

Use something like this:

Dim oRange As Range
Set oRange = ActiveSheet.Range("A1:F2000") 'or whatever your filtered range is
oRange.Cells.SpecialCells(xlCellTypeVisible).Copy
'now paste somewhere


Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
Back
Top