Thread: Formula Reffering to Different Sheets

Well, I don't know a lot about what worksheet functions do, and the following code is not tested to your workbook criteria, and it may be a little crude compared to what the gurus would do, here is a hopeful:

Sub Jonathan78()

With Worksheets("Urenstaat")
    TopRow = .Range("D" & Rows.Count).End(xlUp).Row + 1
    lastrow = .Range("A" & Rows.Count).End(xlUp).Row
    
    If TopRow < lastrow Then
        With .Range("D" & TopRow & ":D" & lastrow)
            .FormulaR1C1 = "=IF(RC[-3]=""Werknemer:"","""",VLOOKUP(INDEX(WERKBLAD!R6C4:R5000C256,MATCH(RC[-3],WERKBLAD!R6C2:R5000C2,0),MATCH(LOOKUP(2,1/(R6C1:RC[-3]=""Werknemer:""),R6C2:RC[-2]),WERKBLAD!R5C4:R5C256,0)),IMPORT!R3C7:R300C11,2,FALSE))"
            .Value = .Value
        End With
    End If
End With

With Worksheets("Urenstaat")
    TopRow = .Range("D" & Rows.Count).End(xlUp).Row + 1
    lastrow = .Range("A" & Rows.Count).End(xlUp).Row
    
    If TopRow < lastrow Then
        With .Range("D" & TopRow & ":D" & lastrow)
            .FormulaR1C1 = "=IF(RC[-3]=""Werknemer:"","""",VLOOKUP(INDEX(WERKBLAD2!R6C4:R5000C256,MATCH(RC[-3],WERKBLAD2!R6C2:R5000C2,0),MATCH(LOOKUP(2,1/(R6C1:RC[-3]=""Werknemer:""),R6C2:RC[-2]),WERKBLAD2!R5C4:R5C256,0)),IMPORT!R3C7:R300C11,2,FALSE))"
            .Value = .Value
        End With
    End If
End With

With Worksheets("Urenstaat")
    TopRow = .Range("D" & Rows.Count).End(xlUp).Row + 1
    lastrow = .Range("A" & Rows.Count).End(xlUp).Row
    
    If TopRow < lastrow Then
        With .Range("D" & TopRow & ":D" & lastrow)
            .FormulaR1C1 = "=IF(RC[-3]=""Werknemer:"","""",VLOOKUP(INDEX(WERKBLAD3!R6C4:R5000C256,MATCH(RC[-3],WERKBLAD3!R6C2:R5000C2,0),MATCH(LOOKUP(2,1/(R6C1:RC[-3]=""Werknemer:""),R6C2:RC[-2]),WERKBLAD3!R5C4:R5C256,0)),IMPORT!R3C7:R300C11,2,FALSE))"
            .Value = .Value
        End With
    End If
End With

End Sub