Automate copy and paste based on condition

Try this:

Sub TransferData()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim R1 As Range
Dim lr1 As Long, n As Long
Dim vA As Variant

Set sh1 = Sheets("Data")
Set sh2 = Sheets("Report")
lr1 = sh1.Range("a" & Rows.Count).End(xlUp).Row
vA = sh1.Range("A1", "A" & lr1).Value
For i = LBound(vA, 1) To UBound(vA, 1)
    If vA(i, 1) = "Employee" Then
        n = n + 1
        sh2.Range("C6").Offset(n - 1).Value = sh1.Range("C" & i).Value
    End If
Next i
End Sub