VBA for date logging

[afrobea_r]

Dear Excel Wizards,

Currently I have a workbook that I use for record keeping everyday.

There are many columns of data in this workbook, one of which is a date column.

As I find it incredibly cumbersome to individually populate every single cell in each column as I move down the rows, I have some in-cell formulas in several cells that returns certain values based on information that I have to manually insert in cells of certain columns.

For the date column, I am using the =Today() function. However, how do I create a macro that dynamically copies all the latest dates (which is today) and then paste as values to the last cell using an adjacent cell as a marker for the last row, so that when it comes to the next day, the dates are archived in order, and the latest date is reflected in the new row?

Below is an example of some column headers that I have. Thank you!

[TABLE="width: 344"]
<tbody>[TR]
[TD]Auto-Region[/TD]
[TD]Source[/TD]
[TD]Week[/TD]
[TD]Date[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Heres an example:

Set sh = Sheets("Sheet1")

With sh
    lrLong = .Range("A" & Rows.Count).End(xlUp).Row
    lrShort = .Range("B" & Rows.Count).End(xlUp).Row
    
    If lrLong > lrShort Then
        .Range(.Cells(lrShort + 1, "B"), .Cells(lrLong, "B")).Value = Date
    End If
End With

It assumes you want to fill in column B based on the last row of column A of sheet called 'Sheet1'.

 

[afrobea_r]

Many thanks sir!!

Heres an example:

Set sh = Sheets("Sheet1")

With sh
    lrLong = .Range("A" & Rows.Count).End(xlUp).Row
    lrShort = .Range("B" & Rows.Count).End(xlUp).Row
    
    If lrLong > lrShort Then
        .Range(.Cells(lrShort + 1, "B"), .Cells(lrLong, "B")).Value = Date
    End If
End With

It assumes you want to fill in column B based on the last row of column A of sheet called 'Sheet1'.