Copying entire rows to a different sheet based on multiple conidtions

Hi all,

I've got a workbook that I've been playing with. I've got macros already installed on a few tabs (I'm guessing I probably could have used one macro to do the whole lot but wasn't sure how so I'm up to 12 separate modules so far I think), but not sure where to begin on the big one....

As below, this is how I want it to work:

1. Information imported from a retail system into the "Orders" sheet
2. Information moved from "Orders" sheet to "CURRENT ORDERS" sheet using the "Import Orders" macro button -I've done this bit and it seems work fine
3. This is where I'm stuck. I want another macro button ("Sort Orders") to copy an entire row to the next blank row on a different sheet, depending on the criteria in columns "F" and "K" and then delete the line from the "Current Orders" sheet i.e if F14 contains the word "Raid" and K14 contains the word "Xbox", then entire line is copied to the next blank row in the "XB Raids" sheet and then this line is deleted from the "Current Orders" sheet. BUT, some lines won't need the criteria in column "K" i.e if F14 contains the word "Iron", the whole line gets copied to the "Iron Banner" sheet and then this line is deleted. There are 9-10 criteria that could be met in column "F", but only 2 (or no) options for column "K".
4. In the other sheets, each lines' status can be changed via a dropbox, which changes the colour of the line
5. Using the "Complete Orders" macro button on the other tabs, any row with a staus of "Complete" is fed over to the "COMPLETED ORDERS" sheet and then deleted -I've done this bit and it seems to work fine also

I started writing a code for Step 3 using the code that I used in Step 5 but I just don't know how to incorporate the criteria and then get it to go to the right place.
Also, each sheet that has a "Complete Orders" macro button feeds onto the same sheet, but as I mentioned earlier I've used a separate module/macro for each one. I'm sure there's probably a better way to do it (using one), but I wasn't sure how.

Is what I want to do even possible?? Any help/suggestions much appreciated.

Hi there,

Also, each sheet that has a "Complete Orders" macro button feeds onto the same sheet, but as I mentioned earlier I've used a separate module/macro for each one. I'm sure there's probably a better way to do it (using one), but I wasn't sure how.

The following routine can be called from the "Complete Orders" button on any of the specific game worksheets.

Please Login or Register  to view this content.

The highlighted values can be changed to suit your own requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi Jaslake,

It seems I can't count and there are actually 13! Although some may be added also over time.
The Criteria are these words:

"Monthly" (needs to be the first done to overrule any other words). Would move to one of the Monthly sheets dependant on Column K
"Challenge". Would move to one of the Raids sheets dependant on Column K
"Hire". Would move to the Private Lessons sheet
"Iron". Would move to the Iron Banner sheet
"Lesson". Would move to the Private Lessons sheet
"Level". Would move to the Levelling sheets
"Raid". Would move to one of the Raids sheets dependant on Column K
"Spindle". Would move to the Spindle sheet
"Story". Would move to the Levelling sheet
"Sword". Would move to the Swords sheet
"Test. Would move to the Levelling sheet
"Trials". Would move to one of the Trials sheets dependant on Column K
"Vault". Would move to one of the Raids sheets dependant on Column K

Hi Greg,

Thanks for that. I'll give it a try!

Hi Jaslake,

There are going to be 2 x options in a dropbox in column K -Xbox or Playstation, so those for example:

If F contains "Monthly" and K contains "Xbox" I want the whole line to copy over to the "XB Monthly" sheet. If K contains "Playstation", it should feed over to the "PS Monthly" sheet. And so on.

I thought about splitting everything by Xbox or Playstation first into another sheet and then re-sorting from there but was hoping to cut out that step if possible....

Thanks

Kiwi

This should give you the idea...

Please Login or Register  to view this content.

Hi Jindon,

That worked PERFECTLY!
I'll have a play and see if I can get all the information feeding over...

Thanks for that

Hi Jindon,

I've tried adding in everything to get all of my information feeding over, but unfortunatley it seems to be missing 30-40 lines and coming up with a "Runtime 1004" error and highlighting this line in the code:

Set rng = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).EntireRow

I'm not sure why it's missing out the lines that it is. Any ideas??
Also, at the end (after the error), the lines that are missed seem like they're hidden, but I have to clear the contents of the cells which makes them visible (except the first line). Not sure if this is something to do with the filter. Is there a way to remove the filter once it hits an error or is finished and stop any missed lines from being hidden??

Change to

Please Login or Register  to view this content.