compile error wrong number of arguments or invalid property assignment [VBA]

Question

this should hopefully be an easy fix. So the code I have below runs fine when I only have 2 parameters from the first sheet being transferred to the second sheet, however when I try to add a third parameter (.Cells(c.row, "F"), my code receives the following error "compile error wrong number of arguments or invalid property assignment". Any thoughts as to why I can't seem to make my code work by adding more parameters when I need to be able to pull additional fields?

Sub copylist()
Dim wb As Workbook
Dim ws_employment As Worksheet
Dim c As Range
Dim i As Long

Set wb = ThisWorkbook
Set ws_employment = wb.Sheets("Employment")

'***This code fails due to adding ws_employment.cells(i, "F") & .cells(c.row, "F")***
'this will add values to the top table on the second sheet if there is an N value
i = 3 'this is the first row in the target sheet
With ws_employment_data
    For Each c In .Range("C3:C11").Cells
        If c.Value = "N" Then
            ws_employment.Range(ws_employment.Cells(i, "G"), ws_employment.Cells(i, "H"), ws_employment.Cells(i, "F")).Value = _
            .Range(.Cells(c.Row, "D"), .Cells(c.Row, "E"), .Cells(c.Row, "F")).Value
            i = i + 1
        End If
    Next c
'this will add values to the bottom table on the second sheet if there is a Y value
i = 12
    For Each c In .Range("C3:C11").Cells
        If c.Value = "Y" Then
            ws_employment.Range(ws_employment.Cells(i, "G"), ws_employment.Cells(i, "H")).Value = _
            .Range(.Cells(c.Row, "D"), .Cells(c.Row, "E")).Value
            i = i + 1
        End If
    Next c
End With



'***This code (is the same as above) works whilst having removed ws_employment.cells(i, "F") & .cells(c.row, "F")***
'this will add values to the top table on the second sheet if there is an N value
i = 3 'this is the first row in the target sheet
With ws_employment_data
    For Each c In .Range("C3:C11").Cells
        If c.Value = "N" Then
            ws_employment.Range(ws_employment.Cells(i, "G"), ws_employment.Cells(i, "H"), ws_employment.Cells(i, "F")).Value = _
            .Range(.Cells(c.Row, "D"), .Cells(c.Row, "E"), .Cells(c.Row, "F")).Value
            i = i + 1
        End If
    Next c
'this will add values to the bottom table on the second sheet if there is a Y value
i = 12
    For Each c In .Range("C3:C11").Cells
        If c.Value = "Y" Then
            ws_employment.Range(ws_employment.Cells(i, "G"), ws_employment.Cells(i, "H")).Value = _
            .Range(.Cells(c.Row, "D"), .Cells(c.Row, "E")).Value
            i = i + 1
        End If
    Next c
End With

MsgBox ("DONE")

End Sub

Answer 1

The Range property only accepts 2 parameters (documentation) so you can only specify the 1st cell and the last cell.

I have also removed wb variable as it doesn't seems necessary.

Try this code:

Sub copylist()

    Dim ws_employment As Worksheet
    Dim c As Range
    Dim i As Long

    Set ws_employment = ThisWorkbook.Sheets("Employment")

    'this will add values to the top table on the second sheet if there is an N value
    i = 3 'this is the first row in the target sheet
    With ws_employment_data
        For Each c In .Range("C3:C11").Cells
            If c.Value = "N" Then
                ws_employment.Range(Replace("F?:H?", "?", i)).Value = _
                .Range(Replace("D?:F?", "?", c.Row)).Value
                i = i + 1
            End If
        Next c
    'this will add values to the bottom table on the second sheet if there is a Y value
    i = 12
        For Each c In .Range("C3:C11").Cells
            If c.Value = "Y" Then
                ws_employment.Range(ws_employment.Cells(i, "G"), ws_employment.Cells(i, "H")).Value = _
                .Range(.Cells(c.Row, "D"), .Cells(c.Row, "E")).Value
                i = i + 1
            End If
        Next c
    End With
    
    MsgBox "DONE"

End Sub