Copy & Paste in Trick Required in VBA

[jawaharprm]

Hi All,

I have 2 sheets in my excel "Copy" & "Paste"
While Pasting if "E) No Requirement" if this reason comes that column should be paste as blank.

is there is any code in VBA can you help on same.
sample file enclosed.

Thanks

 

[AlanSidman]

Option Explicit

Sub foo()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim i As Long, lr As Long
    Set s1 = Sheets("Copy")
    Set s2 = Sheets("Paste")
    Application.ScreenUpdating = False
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        If s1.Range("A" & i) <> "No Requirement" Then
            s2.Range("A" & i) = s1.Range("A" & i)
        End If
        Mext i
    Application.ScreenUpdating = True
        MsgBox "Action Complete"
End Sub