Excel VBA to add current date into empty cells, save it as values and save the file

Question

so the thing is, I have one date column in Excel table. What I would like to do is create a VBA, that would fill in only the empty cells of that table column with current date as values (or TODAY() formula and then transforming the outputs into values) and save the file (ability to close the file after the save would be even better). I am not quite experienced with the VBAs, so help there would be really great.

In a nutshell, the VBA should execute these steps in this order:

1. Fill in the empty cells in "Date created" column (same column name in every table) in Table1, Table2 and Table3, with current date (as value). Table1 is on Sheet1, Table2 is on Sheet2, Table3 is on Sheet3.
2. Save the file
3. Popup message with following: "Data successfully saved. Click "Yes" to close the file. Click "No" to continue working in this file."
4. After clicking on the "Yes", the file would close. After clicking on "No", the file would only save, without closing.

Answer 1

Populate Excel Table Column with Today's Date

Single Sheet

Sub FinalizeSheet()
    
    ' Define constants.
    Const SHEET_NAME As String = "Sheet1"
    Const TABLE_NAME As String = "Table1"
    Const DATE_COLUMN_TITLE As String = "Date"
    Const MSG_TITLE As String = "Finalize Sheet"
    Const MSG_PROMPT As String = _
         "Data successfully saved. Click OK to close the file."
    
    With ThisWorkbook ' workbook containing this code
        ' Populate blank cells with today's date.
        With .Sheets(SHEET_NAME).ListObjects(TABLE_NAME) _
                .ListColumns(DATE_COLUMN_TITLE).Range ' date column
            Dim Today As Date: Today = Date
            Dim RowsCount As Long: RowsCount = .Rows.Count
            If RowsCount = 1 Then ' single cell
                If Len(CStr(.Value)) = 0 Then .Value = Today
            Else ' multiple cells
                Dim Data() As Variant: Data = .Value ' column values to array
                Dim r As Long
                For r = 1 To RowsCount
                    If Len(CStr(Data(r, 1))) = 0 Then Data(r, 1) = Today
                Next r
                .Value = Data ' modified array values to column
            End If
            .EntireColumn.AutoFit
        End With
        ' Save and close.
        .Save
        Dim MsgAnswer As Long: MsgAnswer = MsgBox( _
            MSG_PROMPT, vbQuestion + vbYesNo + vbDefaultButton2, MSG_TITLE)
        If MsgAnswer = vbYes Then .Close SaveChanges:=False ' just got saved
    End With
    
End Sub

Multiple Sheets

Sub FinalizeSheet2()
    
    ' Define constants.
    Const SHEET_NAMES As String = "Sheet1,Sheet2,Sheet3"
    Const TABLE_INDEX As Long = 1
    Const DATE_COLUMN_TITLE As String = "Date Created"
    Const MSG_TITLE As String = "Finalize Sheet"
    Const MSG_PROMPT As String = _
         "Data successfully saved. Click OK to close the file."
    
    With ThisWorkbook
        Dim Today As Date: Today = Date
        Dim SheetNames() As String: SheetNames = Split(SHEET_NAMES, ",")
        Dim Data() As Variant, RowsCount As Long, n As Long, r As Long
        ' In each sheet...
        For n = 0 To UBound(SheetNames)
            ' Populate blank cells with today's date.
            With .Sheets(SheetNames(n)).ListObjects(TABLE_INDEX) _
                    .ListColumns(DATE_COLUMN_TITLE).Range
                RowsCount = .Rows.Count
                If RowsCount = 1 Then ' single cell
                    If Len(CStr(.Value)) = 0 Then .Value = Today
                Else ' multiple cells
                    Data = .Value ' column values to array
                    For r = 1 To RowsCount
                        If Len(CStr(Data(r, 1))) = 0 Then Data(r, 1) = Today
                    Next r
                    .Value = Data ' modified array values to column
                    .EntireColumn.AutoFit
                End If
            End With
        Next n
        ' Save and close.
        .Save
        Dim MsgAnswer As Long: MsgAnswer = MsgBox( _
            MSG_PROMPT, vbQuestion + vbYesNo + vbDefaultButton2, MSG_TITLE)
        If MsgAnswer = vbYes Then .Close SaveChanges:=False ' just got saved
    End With
    
End Sub