Automatically Generated Workbook Using Current Date, Month And Year

Question

I am new with MS EXCEL VBA but I still read some blog about VBA and how it works. I got idea on renaming excel sheet, disabling rows and adding new sheets. But I want to know
how to automatically insert row every day, add new sheet every month, and create new same(default structure) workbook every year add in MS EXCEL 2013 ? How do I do that?

Date today is 1 then tomorrow excel automatically add 1 row for date. If month of January ends then excel automatically add new sheet for February. And it year changes excel generate same default or empty workbook with the current year.

The default structure of the excel, the excel file are located at https://github.com/Ailyn09/project102/blob/master/2017.xlsx

CURRENT CODE

Sub Sample()
        'Disable adding row

        Dim I As Integer
        Dim cbStr As String
        Dim cbCtrl As CommandBarControl
        Application.ScreenUpdating = False
        For I = 1 To 2
            If I = 1 Then
                cbStr = "row"
            Else
                cbStr = "column"
            End If
            For Each cbCtrl In Application.CommandBars(cbStr).Controls
                If cbCtrl.ID = 3183 Then
                    cbCtrl.Enabled = False
                End If
            Next
        Next
        Application.ScreenUpdating = True

        'Copy Existing Sheet And Month As Name 
        ActiveSheet.Copy , Sheets(Sheets.Count)
        ActiveSheet.Name = Month() 
    End Sub

GOT AN IDEA FOR SHEET EVERY MONTH HERE: https://excel.tips.net/T002017_Sheets_for_Months.html

Is it default structure what you have shown on January Sheet? i.e. 5 rows for data for one date and insert another 5 rows for the next date and so on? You should have a date column as well on the sheet which is missing in the current layout. — Subodh Tiwari sktneer
what do you want to happen if you do not run the code for a few days, and then run the code? — jsotola
@jsotola. I want the excel add row automatically every day. And if month changes it will create new sheet and start to the first day but the same formula with the default worksheet. And if year changes it should create new workbook with the default formual. e.g(now is 2017, Workbook name should 2017, and we should have sheet january until july, then july have 24 as a date. The date for july is 1-31 then the last date we should see for the sheet of july should be 1-31 the the SUM row that add all. And next year is 2018 the it will automatically create new workbook 2018 then start to Jan. — Ailyn
Yes, even if I didn't run the code. I should automatically create new row or sheet if you miss to open it. And if you miss to open it a year, it fine you should see new workbook. @jsotola — Ailyn

jsotola jsotola · Accepted Answer · 2017-07-24T05:01:47

here is a start ... just trying out some code ... creates month worksheet if it does not exist ... then adds data to the worksheet

i have to go away for a few days ... will come up with something more when i get back

Sub testDate()

    Debug.Print Format(Now(), "d")
    Debug.Print Format(Now(), "dd")
    Debug.Print Format(Now(), "ddd")
    Debug.Print Format(Now(), "m")
    Debug.Print Format(Now(), "mm")
    Debug.Print Format(Now(), "mmm")
    Debug.Print Format(Now(), "mmmm")
    Debug.Print Format(Now(), "yy")
    Debug.Print Format(Now(), "yyyy")

    Debug.Print Month(Now)
    Debug.Print MonthName(Month(Now))

    Dim ws As Sheets
    Set ws = ActiveWorkbook.Worksheets

    Dim nam As String
    nam = Format(Now(), "mmmm")                     ' month name in local language

    Dim sh As Worksheet
    If Evaluate("ISREF('" & nam & "'!A1)") Then     ' sheet name exists ?
        Set sh = ws(nam)
    Else
        Set sh = ws.Add(after:=ws(ws.Count))
        sh.Name = nam
    End If

' !!!!! use one of the sections below, but NOT both !!!!!
' --------------------------------------------------------------------------------------
    Dim lastCell As Range
    Set lastCell = sh.Range("A" & sh.Rows.Count).End(xlUp)                   ' last used cell in column A
    lastCell.Offset(1).Value = Format(Now(), "to\da\y i\s t\he dd of mmmm")  ' some of the characters must be escaped
' --------------------------------------------------------------------------------------
'   this section corrupts the lastCell value that is used above

'   if fixed number of rows per day, then put daily data in particular rows

'   Dim day As Integer
'   day = Format(Now(), "d")
'   sh.Range("A1").Offset(day).Value = Format(Now(), "to\da\y i\s t\he dd of mmmm")
' --------------------------------------------------------------------------------------

End Sub

Automatically Generated Workbook Using Current Date, Month And Year

1 Answers