Macro to move Entries between Worksheets based on Drop-down Entry

[PANDACHAN]

Hi everyone,

I'm very new to MACROs (started reading about it 2-3 weeks ago) and have since tried to play around with recording and reviewing what I've recorded.

I require my work sheet to do the following, to be able to move entries (entire row cut and pasted) from 'WAREHOUSE 1' to 'WAREHOUSE 2' based on entry in Column 'L' (and vice versa)

So far when I've tried to record the trail, it does not record the first stage (when I choose an entry from my drop down menu).

Any help would be appreciated. As mentioned, I'm very new to MACROs so if you could explain in the simplest terms possible that would be great.

Thankful for everyone's time!

Juno

[humdingaling]

see attached

using worksheet_change event on both sheets

[PANDACHAN]

Thank you humdingaling!

The method is exactly what I'm looking for. Just to build upon this, what would I need to change within the coding so it will still shift to a specific worksheet based on multiple entries within the drop down list (so rather than the two I have for e.g. if entries 1-5 within a drop down list = it moves to worksheet 2 and only entry 6 moves to worksheet 1 etc...) I can't seem to figure this out from your code.

If I need to attach another example do let me know.

Thanks so much, really appreciate your help on this.

Juno

[humdingaling]

what do you exactly mean by multiple entries?
you mean more than just warehouse 1 and 2? or you want to move more rows at once?

[PANDACHAN]

Apologies, should have been clearer. I've reattached the sample worksheet but with adjustments.

Basically I want to make it so the MACRO will move all entries with DROPDOWN: STORE 1 - 9 and WAREHOUSE 2 to worksheet 'WAREHOUSE 2'. I was unable to expand on the MACRO you provided...

Hope this is clear.

Again, forever grateful for your help.

Juno

[jaslake]

Hi PANDACHAN

Try this (not tested...drop downs don't appear in sample File)...

Please Login or Register  to view this content.

[PANDACHAN]

Thanks for the quick response jaslake. The drop down options are in Column L. Expanding on your MACRO, how would I make it so only specific entries are moved to another worksheet (this is based on the drop down menu entry selected) as so far the rule provided moves all entries.

[jaslake]

Hi PANDACHAN

The Sample File I see does not have Dropdowns in Column L in ANY of the Worksheets.

What are the Rules...you've defined them as

the MACRO will move all entries with DROPDOWN: STORE 1 - 9 and WAREHOUSE 2 to worksheet 'WAREHOUSE 2'

The Code I provided does this...if it's NOT WAREHOUSE1 it gets moved...if it's WAREHOUSE1 nothing happens.

Please explain again...please show me the Dropdowns.

[humdingaling]

Please Login or Register  to view this content.

[PANDACHAN]

Thanks for your reply.

So just to breakdown what is required in this situation. The attached file has three worksheets: WAREHOUSE 1, WAREHOUSE 2, WAREHOUSE 3. In column L in each of these, there is a drop down menu with the options:

WAREHOUSE 1
WAREHOUSE 2
STORE 1 - 9

Basically I want the MACRO to be able to conduct the following:

For WORKSHEET 1 if:

Column L = WAREHOUSE 1 - remains in WORKSHEET 1
Column L = WAREHOUSE 2 - move that row to WORKSHEET 2.
Column L = STORE 1-9 - move rows to WORKSHEET 3

I want the above rules to apply to WORKSHEET 2 and 3.

I hope I've broken it down enough. So far the rule provided (very grateful for this) moves all entries to WORKSHEET 2.

Thanks again.

[humdingaling]

Don't understand why you wouldnt have the whole situation to begin with in the first place....

essentially you just want another case condition in there

[loydancer]

Excuse the intrusion here. I'm VERY new (first post). So if I'm being out-of-order, I apologize. This code works perfectly for my personal situation. The only difference I would request - is there a way to copy only certain cells out of that target row to the new worksheet, instead of copying the whole row? In other words, not all of the columns on the first sheet are on the second sheet. Just selected ones. Thank you so much!

[humdingaling]

loydancer

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

ps when you do start own thread
attach sample of your file as well, it will be slightly different coding

[jaslake]

@ humdingaling

You're doing fine...I'll leave this in your capable hands.

[PANDACHAN]

Thanks again humdingaling.

I was hoping once I had the first condition I would be able to figure it out... (unfortunately this was not the case haha...)

I require the final condition mentioned: (Column L = STORE 1-9 - move rows to WORKSHEET 3)

Would I only need to add this condition in?

Select Case Target.Value
Case "WAREHOUSE 3"
Rows(Target.Row).EntireRow.Copy Sheets("STORE 1").Cells(Sheets("WAREHOUSE 3").Cells(Rows.Count, "b").End(xlUp).Row + 1, 1) 'copy row to other sheet
Rows(Target.Row).Delete 'delete row off current sheet

(and then again for STORE 2, STORE 3 etc...)

Hope this is clear?

[humdingaling]

so
warehouse 1 > worksheet 1
warehouse 2 > worksheet 2
warehouse 3 > worksheet 3
anything else ..ie store 1-9 move to worksheet 3??

if this is what you want

make every sheet have 1-2-3

Please Login or Register  to view this content.

then to clean up the code and make it stop doing work where it doesnt need

ie on worksheet 3

Please Login or Register  to view this content.

[PANDACHAN]

Yes I wanted the fourth condition you mentioned:

'anything else ..ie store 1-9 move to worksheet 3??'

I'm assuming I just replace the location to one of these e.g.

Case "WAREHOUSE 3"
Rows(Target.Row).EntireRow.Copy Sheets("STORE 1").Cells(Sheets("WAREHOUSE 3").Cells(Rows.Count, "b").End(xlUp).Row + 1, 1) 'copy row to other sheet
Rows(Target.Row).Delete 'delete row off current sheet

Thanks for your patience.

[PANDACHAN]

I just realised that you already created the condition for this with:

Case Else
Rows(Target.Row).EntireRow.Copy Sheets("WAREHOUSE 3").Cells(Sheets("WAREHOUSE 3").Cells(Rows.Count, "b").End(xlUp).Row + 1, 1) 'copy row to other sheet
Rows(Target.Row).Delete 'delete row off current sheet

Ok it all makes sense now thanks.

Is it possible to make it so I actually put what the entry is? so will the following work:

Case Else
Rows(Target.Row).EntireRow.Copy Sheets("STORE 1","STORE 2","STORE 3").Cells(Sheets("WAREHOUSE 3").Cells(Rows.Count, "b").End(xlUp).Row + 1, 1) 'copy row to other sheet
Rows(Target.Row).Delete 'delete row off current sheet

Thanks again

[humdingaling]

please use code tags when you are pasting code....it makes the post a whole lot easier to read

no you cant do it like this

Please Login or Register  to view this content.

you can make it something like below
For warehouse 1 code

Please Login or Register  to view this content.

however you must have a tab for each option in drop down list
otherwise error will occur

attached sheet
warehouse 1-2-3 all work but if you pick store 1-9 it will create error
so either dont put option in drop down or create sheet named "exactly" what drop down option is called