Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
moving rows based on criteria
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
moving rows based on criteria
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 equal "AquaCulture" to worksheet "Aquaculturist" Thanks Phil . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
moving rows based on criteria
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 "Patrick Molloy" wrote in message ... 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 equal "AquaCulture" to worksheet "Aquaculturist" Thanks Phil . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
moving rows based on criteria
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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving rows with multiple criteria | Excel Worksheet Functions | |||
Moving cells based on certain criteria | Excel Discussion (Misc queries) | |||
Moving Rows to another sheet based on an entry | Excel Worksheet Functions | |||
Moving rows from one sheet to another based on a particular cell v | Excel Discussion (Misc queries) | |||
Sum based on criteria in rows and columns | Excel Discussion (Misc queries) |