Copy an entire row to a different sheet if a certain cell in a column matches criteria

[dmchase]

I need to create an excel macro that searches through a document and if a cell in column A matches a certain name, for example "Name1" then I need it to put the corresponding row into sheet 2. There will be multiple rows that match this criteria. I also need it search column A and anything with "Name2" put that in Sheet3, and so on...

There are approximately 11 different values that I need the macro to do this for, and copy all the corresponding rows into the appropriate sheet. I would greatly appreciate any assistance that anyone can offer.

Thank you.

[gmk]

dmchase

Try this. Hard code the names where indicated surrounded with quotes as in "Fred". Add another Case for an additional name together with a wsheet or delete as appropriate.


hth

gmk

Sub CopyNameRows()

    Dim rng As Range, tbl As Range
    Dim nLastRowA As Long
    Dim nFirstRow As Long

    Application.EnableEvents = False

    With ThisWorkbook.Sheets(1)

        nFirstRow = 2
        nLastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row

        Set tbl = .Range(.Cells(nFirstRow, "A"), .Cells(nLastRowA, "A"))

        For Each rng In tbl

            Select Case rng
            Case    ''Name1
                rng.EntireRow.Copy Sheets(2).Cells(rng.Row, "A")
            Case    ''Name2
                rng.EntireRow.Copy Sheets(3).Cells(rng.Row, "A")
            Case    ''Name3
                rng.EntireRow.Copy Sheets(4).Cells(rng.Row, "A")
            Case    ''Name4
                rng.EntireRow.Copy Sheets(5).Cells(rng.Row, "A")
            Case    ''Name5
                rng.EntireRow.Copy Sheets(6).Cells(rng.Row, "A")
            Case    ''Name6
                rng.EntireRow.Copy Sheets(7).Cells(rng.Row, "A")
            Case    ''Name7
                rng.EntireRow.Copy Sheets(8).Cells(rng.Row, "A")
            Case    ''Name8
                rng.EntireRow.Copy Sheets(9).Cells(rng.Row, "A")
            Case    ''Name9
                rng.EntireRow.Copy Sheets(10).Cells(rng.Row, "A")
            Case    ''Name10
                rng.EntireRow.Copy Sheets(11).Cells(rng.Row, "A")
            Case    ''Name11
                rng.EntireRow.Copy Sheets(12).Cells(rng.Row, "A")
            Case Else
            End Select

        Next rng
        Application.EnableEvents = True

    End With

End Sub

[Phatbassline]

Hi
The above query and code seems to be along the lines of my query.

Where i am lost is that the keys words i am targeting are "corporate", "finance", "i.t" etc....

With the worksheets named accordingly...

What do i need to edit to make this change?

Any help greatly appreciated..... Great Site BTW

[arlu1201]

Phatbassline,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[Phatbassline]

Opps...sorry