VBA to count number of new rows and add date in adjacent column

[hb_123]

Hi there, I'm quite new to VBA so any help is appreciated!!

I have a macro that opens Workbook A, copies the data and pastes it in Workbook B.(Sheet 1) below existing data.
What i would like to do is have a code that can count the number of rows that have been added and paste the date (which located in Sheet1 cell E2) in column D.

So for example, in the below, i would like the VBA to count the last three rows (newly pasted below existing data), and paste in the date from E2 into the the blank rows.

City	Country	Rainfall	Date
London	UK	5049​	02/05/2017​
Manchester	UK	900​	02/05/2017​
London	UK	690​
Manchester	UK	500​
New York	USA	594​

<tbody>
</tbody>

Any help at all would be very very welcome!

 

[XLfriend777]

Hi hb_123 You might try the code below.

Sub hb_123_copy_date()
Dim i As Integer
i = 1
    While Cells(i, 1).Value <> ""
        If Cells(i, 4).Value = "" Then
'Copies contents of E2 to empty cell in column D
            Cells(i, 4).Value = Cells(2, 5).Value
            
        End If
    i = i + 1
    Wend
End Sub