Copy Specific Columns from a Filtered Range

Saarang,

Are you putting headers into the Prod and Innov sheets? I can modify the code if you are not... but right now it would be best if you do, if not you will have a blank row at the top of those sheets with this code, but as I said it can be dealt with if required.

Sub Saarang()

Dim ws As Worksheet, wsprod As Worksheet, wsinn As Worksheet
Dim lr As Long, i As Long

Set ws = Worksheets("Source")
Set wsprod = Worksheets("Prod")
Set wsinn = Worksheets("Innov")
lr = ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 5 To lr
    If ws.Range("L" & i).Text <> "#N/A" And ws.Range("W" & i).Value <> "Innov" Then
        wsprod.Range("A" & wsprod.Range("A" & Rows.Count).End(xlUp).Row + 1).Value = "Add"
        wsprod.Range("B" & wsprod.Range("A" & Rows.Count).End(xlUp).Row).Value = ws.Range("P" & i).Value
        wsprod.Range("C" & wsprod.Range("A" & Rows.Count).End(xlUp).Row).Value = ws.Range("F" & i).Value
    End If
    If ws.Range("L" & i).Text <> "#N/A" And ws.Range("W" & i).Value <> "Prod" Then
        wsinn.Range("A" & wsinn.Range("A" & Rows.Count).End(xlUp).Row + 1).Value = "Add"
        wsinn.Range("B" & wsinn.Range("A" & Rows.Count).End(xlUp).Row).Value = ws.Range("S" & i).Value
        wsinn.Range("C" & wsinn.Range("A" & Rows.Count).End(xlUp).Row).Value = ws.Range("F" & i).Value
    End If
Next i

End Sub