Automatically generate list of dates for the month on multiple sheets

[n0n0n0]

Hi,

I have 19 sheets that all have a table like this:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Loan Balances[/TD]
[TD]Daily Interest[/TD]
[/TR]
[TR]
[TD]03/30/18[/TD]
[TD]XXX.XXX[/TD]
[TD]Loan*rate[/TD]
[/TR]
[TR]
[TD]03/31/18[/TD]
[TD]XXX.XXX2[/TD]
[TD]Loan*rate[/TD]
[/TR]
</tbody>[/TABLE]


I would like to have all 19 sheets to automatically generate all the dates of the current month when such month arrive. For example, since it's April, on all 19 sheets the table will automatically add april 1st to April 30th, 2018 to the table above and other related columns.

Thank you very much for your time and wisdom.

 

[mumps]

Format column A of each sheet as a 'Date". Place this macro in the code module for ThisWorkbook. Save the workbook as a macro-enabled file. The macro assumes that every sheet will have a header such as "Date" in cell A1. Every time you open the file, it will check to see if it is the first day of the month. If it is, the days for each month will be placed in column A starting in cell A2.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim lDay As Long
    If Day(Date) = 1 Then
        For Each ws In Sheets
            ws.Range("A2") = Date
            lDay = Day(VBA.DateSerial(VBA.Year(Range("A2")), VBA.Month(Range("A2")) + 1, 0))
            ws.Range("A2").AutoFill ws.Range("A2:A" & lDay), xlFillDays
        Next ws
    End If
    Application.ScreenUpdating = True
End Sub