Hello guys,

Could you please help me out with this code. So this retrieves all rows with #N/A in G column from another worksheet "cam" and puts it under "sap". But column's data doesn't go in the same place, it should go to these columns:

Column A from 'cam' to column A in 'sap'
Column B from 'cam' to column G in 'sap'
Column C from 'cam' to column H in 'sap'
Column D from 'cam' to column I in 'sap'
Column E from 'cam' to column J in 'sap'
Column F from 'cam' to column F in 'sap'

File: sap-cam.xlsx

Thanks in advance!

    Sheets("cam").Select
    Range("G1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-6],sap!C[-6],1,0)"
    Range("G1").AutoFill Destination:=Range("G1:G" & Range("A" & Rows.Count).End(xlUp).Row)
    Columns("G:G").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Dim ws10 As Worksheet:   Set ws10 = Sheets("cam")
    Dim ws30 As Worksheet:   Set ws30 = Sheets("sap")
    Dim rCell As Range

    For Each rCell In ws10.Range("G1:G" & ws10.Range("G" & Rows.Count).End(xlUp).Row)
    If IsError(rCell) Then
        ws30.Range("G" & Rows.Count).End(xlUp).Offset(1, -6).EntireRow.Value = rCell.EntireRow.Value
    End If
Next rCell