Send entire row based off value

[VBASuccessStory]

I have a spreadsheet hat has multiple lines that have an identifier in column B. I'm wondering how I would go about building a macro to send the entire row while keeping the existing row to the second worksheet based off it's value.
For example, send 0A on column B to 2nd worksheet

as well as
send IO, IB, and IC to the 3rd worksheet. I think if I get an example I can add multiple lines to reflect all my unique identifier codes.

 

[My Aswer Is This]

Try this:

Sub My_Case()
'Modified  10/24/2018  6:35:20 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Dim Lastrowb As Long
Dim Lastrowc As Long
For i = 1 To Lastrow
    With Cells(i, 2)
        Select Case .Value
            Case "IO", "IB", "IC"
                Lastrowc = Sheets(3).Cells(Rows.Count, "B").End(xlUp).Row + 1
                Rows(i).Copy Sheets(3).Rows(Lastrowc)
               
          Case "0A"
                Lastrowb = Sheets(2).Cells(Rows.Count, "B").End(xlUp).Row + 1
                Rows(i).Copy Sheets(2).Rows(Lastrowb)
                
          End Select
    End With
Next
Application.ScreenUpdating = True
End Sub

 

[VBASuccessStory]

Try this:

Sub My_Case()
'Modified  10/24/2018  6:35:20 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Dim Lastrowb As Long
Dim Lastrowc As Long
For i = 1 To Lastrow
    With Cells(i, 2)
        Select Case .Value
            Case "IO", "IB", "IC"
                Lastrowc = Sheets(3).Cells(Rows.Count, "B").End(xlUp).Row + 1
                Rows(i).Copy Sheets(3).Rows(Lastrowc)
               
          Case "0A"
                Lastrowb = Sheets(2).Cells(Rows.Count, "B").End(xlUp).Row + 1
                Rows(i).Copy Sheets(2).Rows(Lastrowb)
                
          End Select
    End With
Next
Application.ScreenUpdating = True
End Sub

thank you, that worked well for me. I'm going to have to tinker with it and add my departments and see what I can do, thank you!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Just add as may case statements as needed.

I gave you your examples.
I hope that helped you see how it works.

It's always nice to see users wanting to learn and not just looking for us to provide the entire answer.

 

[VBASuccessStory]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Just add as may case statements as needed.

I gave you your examples.
I hope that helped you see how it works.

It's always nice to see users wanting to learn and not just looking for us to provide the entire answer.

thanks, well maybe one day I can be like you and help teach VBA or other excel functions to others.. In the mean time, i've adjusted the code to add roughly 60 lines.

How do I adjust the VBA code to delete line items that might be in the other worksheets? I'm copying information from worksheet 1 into wksht 2, 3, 4, etc. There's already old data in these other worksheets that I want the extracted info from worksheet 1 to replace the old information. Any guidance would be greatly appreciated.

 

[My Aswer Is This]

I'm not sure what you now wanting.

My original code looks in column B for certain values
If value in column B equals a or c or g or q
For example this row of data will be copied to sheet 2 to 3

You never mentioned you wanted this row of data to overwrite some other row in the other sheet.

So I need more information about what you want.

Show me your new code where you added in 6o more lines of code.

You said earlier:

thank you, that worked well for me

But my script added new rows of data to sheets 2 and 3 it did not overwrite existing data.​

 

[My Aswer Is This]

You said in your first post:

I think if I get an example I can add multiple lines to reflect all my unique identifier codes.

Normally when doing things like this there must be some relationship

Why for example does :

IO, IB, and IC

Get copied to sheet 3

What is the relationship here?


And your now saying you have more the 3 sheet if I understand.


And if you want to overwrite row in other sheet how do we know how to match up the rows.​

_{^{<strike>
</strike>}}​