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" ?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Country[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]New York[/TD]
[TD]N/A[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]New York[/TD]
[TD]NY[/TD]
[TD]United States[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Country[/TD]
[TD]Note[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]New York[/TD]
[TD]N/A[/TD]
[TD]United States[/TD]
[TD]Please Check[/TD]
[/TR]
</tbody>[/TABLE]



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.