with Worksheets isn't working correctly

[288enzo]

I don't understand why this isn't pasting the value of C4 into C5:C1000. When I run it in any sheet (not Employees) it works except for the pasting. It will paste the range on whatever sheet I'm on, not "Employees", unless of course I'm on the sheet "Employees".

            With Sheets("Employees").Range("C4")
            .Formula = "=LEFT(B4,2)"
            .Formula = .Value
            .Copy Range("C5:C1000")
            End With

I also tried

            .Range("C4").Copy Range("C5:C1000")

            .Range("C4") = Range("C5:C1000")

            .Range("C4").Value = Range("C5:C1000").Value

On a side note, I'd like this to run only if C4 is empty/blank.

If Sheets("Employees").Range("C4")="" Then
            With Sheets("Employees").Range("C4")
            .Formula = "=LEFT(B4,2)"
            .Formula = .Value
            .Copy Range("C5:C1000")
            End With
End If

Thank you

 

[RoryA]

You forgot the dot in front of the target range:

.Copy .Range("C5:C1000")

 

[288enzo]

You forgot the dot in front of the target range:

.Copy .Range("C5:C1000")

Thanks, but even with the change it isn't copying C4 and pasting into C5:C1000

            With Sheets("Employees").Range("C4")
            .Formula = "=LEFT(B4,2)"
            .Formula = .Value
            .Copy .Range("C5:C1000")
            End With

 

[288enzo]

I had to add the Sheets("Employees") again, seems strange to me. But what do I know

If Sheets("Employees").Range("C4") = "" Then
            With Sheets("Employees").Range("C4")
            .Formula = "=LEFT(B4,2)"
            .Formula = .Value
            .Copy Sheets("Employees").Range("C5:C1000")
            End With
End If

 

[RoryA]

Sorry - I overlooked your with block holding a cell reference, so it would be:

.copy .worksheet.Range("C5:C1000")