Automate copy and paste based on condition

I am trying to automate the following but not sure what code to use for it. In sheets("Data") if any value in column A = Employee then copy the corresponding value in column C and paste in sheets("Report") in col C6 and onwards. Many thanks.

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

I got an error saying Method Range of Object worksheet failed and it highlighted this line

"vA = sh1.Range("A", "A" & lr1).Value"

Originally Posted by rlsublime

I got an error saying Method Range of Object worksheet failed and it highlighted this line

"vA = sh1.Range("A", "A" & lr1).Value"

What's in column A of the sheet "Data"?

I have attached the sample file along with this message. Basically, I am trying to actomate all the fields in Report tab but was trying to start with EIN in col c of report tab. many thanks.

Originally Posted by rlsublime

I got an error saying Method Range of Object worksheet failed and it highlighted this line

"vA = sh1.Range("A", "A" & lr1).Value"

As I posted it, that line should be:

vA = sh1.Range("A1", "A" & lr1).Value

Note "A1" NOT "A"

wow. that worked beautifully. Thanks. Any idea how i could populate the vacation hours and overtime hours in the report tab.

From the workbook you posted, it's not clear that you have set up the Data sheet in a way that makes finding vacation and O/T hours consistent from one employee to the next. If you revise that sheet so that each Employee record contains those quantities (even if they are zero), the code I posted can be easily adapted to do this.

unfortunately this is how the data is pulled from the system and there will be multiple data extracts which is why I am attempting to automate. Is there any way to say if there is employee totals in A col then copy data from E col and paste in E col of report tab?