How to copy a row to another sheet based on if then

Question

I'm trying to write a macro that will copy a row to another sheet if certain values are met. There are multiple possible destinations. This is what I've pieced together, but I'm sure it's messy. Basically, if in column 14 there is "N/A" and in column 8 there is "APP" then copy that to the APP tab. And so on for Angie, Cathy, etc.

Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Reconciliation")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("APP")
Dim ws3 As Worksheet: Set ws3 = ThisWorkbook.Sheets("Angie")
Dim ws4 As Worksheet: Set ws4 = ThisWorkbook.Sheets("Cathy")
Dim ws5 As Worksheet: Set ws5 = ThisWorkbook.Sheets("Cory")
Dim ws6 As Worksheet: Set ws6 = ThisWorkbook.Sheets("Curt")

For Each i In ws1.Range("A1:A1000")
    If ws1.Cells(i, 14) = "#N/A" Then
        If ws1.Cells(i, 8) = "APP" Then
            ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 2).End(xlUp).Row + 1)
        End If
    End If
Next i

Why loop and not [AUTOFILTER](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s)? — Siddharth Rout, Nov 19 '13 at 17:19

Tim Williams · Answer 1 · 2013-11-19T17:41:38.693

0

This will copy the row if there's a sheet with a matching name:

Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Reconciliation")
Dim ws

For Each i In ws1.Range("A1:A1000").Cells
    If ws1.Cells(i.Row, 14).Value = cverr(2042) Then
            Set ws = Nothing
            On Error Resume Next
            Set ws = ThisWorkbook.Sheets(ws1.Cells(i.Row, 8).Value)
            on error goto 0
            If Not ws Is Nothing Then            
                i.EntireRow.Copy ws.Rows(ws.Cells(ws.Rows.Count, 2).End(xlUp).Row + 1)
            End If
        on error goto 0
    End If
Next i

edited Nov 19 '13 at 17:41

answered Nov 19 '13 at 17:35

Tim Williams

154,628
8
97
125

Thanks Tim. I am terribly new to this. Can you explain what is happening in your code? What about the other sheets? How do I add the next lines for the other sheets? – user3009860 Nov 19 '13 at 17:56
That's all the code. It gets the cell value from column8 and tries to set `ws` to a worksheet with that name (ignoring any error if the sheet doesn't exist). If the sheet does exist then `ws` is not = `Nothing` and it copies the row over to that sheet. Basically it will copy any row over to a sheet in the workbook with the same name as the value in column8. Also fixed your check for #N/A (unless your cells actually have the text "#N/A" in which case you can switch back to your version) ;-) – Tim Williams Nov 19 '13 at 18:17
Thanks Tim. It actually is a cell that has "N/A" as an output from a formula. I changed the Value = cverr(2042) to Value = "N/A" and I get a type mismatch error. Any suggestions? – user3009860 Nov 19 '13 at 19:36
If the #N/A is coming from a formula then leave it as I had it. – Tim Williams Nov 19 '13 at 20:10
Add `Debug.Print ws1.Cells(i.Row, 14).Value` just before the `If` line. What do you see (in the Immediate pane in the VB editor) ? – Tim Williams Nov 19 '13 at 22:54

How to copy a row to another sheet based on if then

1 Answers1