How To Move Entire Row To Various Sheet Based On Cell Value In Excel?

[suicidalporcupine]

Hello everyone,

all the tutorials I've search has the same result. Most tutorials shows you how to copy the Row from Sheet 1 to Sheet 2 only. Maybe I am not searching it properly? What I need is to COPY Row from Sheet 1 to Sheet 2 OR Sheet 3 OR Sheet 4 depending on the value on Column "M" Can someone please direct me to that site or show me the vba code on how to make it work? So example, if value = Daniel, then I want it to copy that row to "Daniel" Sheet. but if the value is "Brian" then it will copy that row to "Brian" sheet.

Thanks a bunch

 

[dmt32]

Hi,
assuming your data has a row with headers then have a look at code I posted (#post 2) here:https://www.mrexcel.com/forum/excel...t-another-based-value-column-first-sheet.html

and see if it does what you want.

To use, Following instructions provided in post

Dave

 

[suicidalporcupine]

Yes, the actual list starts at A2.

Trying to read and understand the codes.

Will this work if I have almost a thousand rows. Because I am dealing with cases, and I want to know which lawyer is dealing with which cases.

what if there's already a "named" sheet. will it recreate a new sheet or copy and paste onto the existing sheet?

in which part do I enter the names of the sheets for my project?

 

[suicidalporcupine]

also, instead of Column B, can I change to Column M? and if so, what/where can I change that within the code?

 

[Michael M]

Did this in the car....so is untested !!

Sub MM1()
Dim lr As Long, r As Long, ls As Long
lr = Cells(Rows.Count, "M").End(xlUp).Row
For r = 2 To lr
    ls = Sheets(Range("M" & r).Value).Cells(Rows.Count, "A").End(xlUp).Row
    Rows(r).Copy Sheets(Range("M" & r).Value).Range("A" & ls + 1)
    ls = ls + 1
Next r
End Sub

 

[dmt32]

also, instead of Column B, can I change to Column M? and if so, what/where can I change that within the code?

code is flexible and should not need any changes. when the inputbox appears, just click on the column heading you want to filter - data will be copied to existing worksheet(s) or a sheet will be created if it does not exist.

Dave

 

[=ODIN=]

Did this in the car....so is untested !!

Sub MM1()
Dim lr As Long, r As Long, ls As Long
lr = Cells(Rows.Count, "M").End(xlUp).Row
For r = 2 To lr
    ls = Sheets(Range("M" & r).Value).Cells(Rows.Count, "A").End(xlUp).Row
    Rows(r).Copy Sheets(Range("M" & r).Value).Range("A" & ls + 1)
    ls = ls + 1
Next r
End Sub

I love it! Car Coding is beyond awesome! And yet I hope I'm not on the road with you :P

 

[Michael M]

Ummmm....I wasn't in the drivers seat...

 

[suicidalporcupine]

Thank you very much btw. this helps a lot. and if I want to any kind of code in the future, where do I exactly can place them? before the End Sub?

 

[Michael M]

I don't exactly follow you.....but you can put more subs in the module, so they work seperately
OR
you can insert code within the existing sub.
If you have further issues with code.....simply post back here !!