VBA IF Then Statement

[unknownymous]

Hello Guys,

Is there any way where I can copy the whole row if the country is United States and the State is "N/A" ?

Code	City	State	Country
001	New York	N/A	United States
002	New York	NY	United States

<tbody>
</tbody>

I want to copy the data in another tab like this and header will be pasted starting in F1 (code):

Code	City	State	Country	Note
001	New York	N/A	United States	Please Check

<tbody>
</tbody>

Thanks for the help.

 

[Scott T]

Try

Sub movena()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lr1 As Long
Dim lr2 As Long


Set ws1 = Sheets("sheet1")
Set ws2 = Sheets("sheet2")
lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row


ws2.Range("F1") = "Code"
ws2.Range("G1") = "City"
ws2.Range("H1") = "State"
ws2.Range("I1") = "Country"
ws2.Range("j1") = "Note"


lr2 = ws2.Cells(Rows.Count, "f").End(xlUp).Row


For x = 2 To lr1
    If UCase(ws1.Cells(x, 4)) = "UNITED STATES" And UCase(ws1.Cells(x, 3)) = "N/A" Then
        ws1.Range("A" & x & ":D" & x).Copy ws2.Range("F" & lr2 + 1)
        ws2.Cells(lr2 + 1, "J") = "Please Check"
        lr2 = ws2.Cells(Rows.Count, "f").End(xlUp).Row
    End If
    
Next x


End Sub

 

[unknownymous]

This is great! Thanks for the help.

 

[DanteAmor]

Try this

Sub Macro2()
    Sheets("Sheet1").Range("A1").AutoFilter 3, "N/A"
    Sheets("Sheet1").Range("A1").AutoFilter 4, "United States"
    Sheets("Sheet1").AutoFilter.Range.Copy Sheets("Sheet2").Range("F1")
    Sheets("Sheet1").ShowAllData
End Sub

 

[unknownymous]

Thanks Dante!

 

[Rick Rothstein]

Try

Sub movena()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lr1 As Long
Dim lr2 As Long

Set ws1 = Sheets("sheet1")
Set ws2 = Sheets("sheet2")
lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row

[B][COLOR="#FF0000"]ws2.Range("F1") = "Code"
ws2.Range("G1") = "City"
ws2.Range("H1") = "State"
ws2.Range("I1") = "Country"
ws2.Range("j1") = "Note"[/COLOR][/B]

lr2 = ws2.Cells(Rows.Count, "f").End(xlUp).Row

For x = 2 To lr1
    If UCase(ws1.Cells(x, 4)) = "UNITED STATES" And UCase(ws1.Cells(x, 3)) = "N/A" Then
        ws1.Range("A" & x & ":D" & x).Copy ws2.Range("F" & lr2 + 1)
        ws2.Cells(lr2 + 1, "J") = "Please Check"
        lr2 = ws2.Cells(Rows.Count, "f").End(xlUp).Row
    End If
    
Next x

End Sub

You might find it interesting to know that the five highlighted lines of code above can be replaced by this single line of code...

ws2.Range("F1:J1") = Array("Code", "City", "State", "Country", "Note")

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.