Copy cell and paste to a dynamic range in another worksheet

[Dokat]

Hi,

I have a file with multiple sheets. I would like to copy a cell from Sheet("Sta") cell:O18 and paste it to sheet "Summary" column D, but range will be based on first row and last row of column "C". In attached example "Sta" cell O18 (Test) is copied to column D3 through D18 based on the first and last row of column C. This range going to be dynamic. Can this be done? and Appreciate any help with code.

 

[My Aswer Is This]

Try This:

Sub Paste_Me()
'Modified  2/16/2022  6:24:33 AM  EST
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("Summary").Cells(Rows.Count, "C").End(xlUp).Row
Dim ans As String
ans = Sheets("Sta").Range("O18").Value
With Sheets("Summary")
    For i = 1 To Lastrow
        If .Cells(i, 3).Value <> "" Then .Cells(i, 4).Value = ans
Next
End With

Application.ScreenUpdating = True
End Sub