VBA Rolling Date

[bmkelly]

Hello,

I am trying to figure out how I can have VBA insert today's current date into a specific cell.

For example in cell A28 we have our start date as 3/9/2020, cell A29 is 3/10/2020, A30 is 3/11/2020 and so on. I only need Weekdays to be included and not Weekends.

I know this can be done simply with a click and drag formula but I am working on a larger code and this would be the beginning stage of the entire coding.

 

[6StringJazzer]

Insert today's date into what specific cell? Also, I don't see how your description about A28 and so on addresses inserting today's date.

If you just need to insert today's date in a cell, just hit CTRL+; which is easier than invoking VBA to do it.

 

[bmkelly]

Insert today's date into what specific cell? Also, I don't see how your description about A28 and so on addresses inserting today's date.

If you just need to insert today's date in a cell, just hit CTRL+; which is easier than invoking VBA to do it.

Thanks, I might have to just go about the CTRL + option. I wanted to be able to run a macro that will insert the current date in a cell and then I would be able to continue on with the rest of my code to enter the data I needed for that current date.

Example:

Today is 3/27/2020 so if I had the correct code I could just run the macro and it would insert todays date in the next available cell in Column A and the data for that date would follow beside it in columns B-K. Then Monday when I go in again it would insert Mondays date (3/30/2020) in te next available cell in Column A (below 3/27/2020 date) and the data for 3/30/2020 would then follow beside it as well in columns B-K.

Hope that makes a little more sense

 

[MrTomasz]

Try:

Sub test()
    Dim lastRow As Integer
    If Format(Now, "dddd") <> "Saturday" And Format(Now, "dddd") <> "Sunday" Then
        Set sht = ActiveSheet
        With sht
            lastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
            If .Range("A" & lastRow).Value <> Format(Now, "m/d/yyyy") Then .Range("A" & lastRow + 1).Value = Format(Now, "m/d/yyyy")
        End With
    End If
End Sub