Create daily tabs for a whole month

[I am hopeful]

Hello,

My goal is simply to add a VBA code to a blank excel workbook that automatically creates a tab for each day of the month. I also need help learning the exact process for executing the code.

Any help would be appreciated and I thank you in advance for your time.

 

[Joe4]

Welcome to the Board!

For the current month?
If not, then how do we know for which month?

And how exactly do you want each tab to be named?

 

[Joe4]

Here is VBA code that will create a new tab for each day of the month, and will simply name it the day of the month (i.e. 1, 2, 3, etc).

Sub InsertDailyTabs()

    Dim wb As Workbook
    Dim lDay As Long
    Dim d As Long
    
    Application.ScreenUpdating = False

'   Capture active workbook
    Set wb = ActiveWorkbook

'   Find last day of current month
    lDay = Format(Application.WorksheetFunction.EoMonth(Date, 0), "d") + 0
    
'   Loop through days of the month
    For d = 1 To lDay
'       Insert new sheet at end of workbook and name day number
        wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = d
    Next d
    
    Application.ScreenUpdating = True
    
End Sub

 

[I am hopeful]

Here is VBA code that will create a new tab for each day of the month, and will simply name it the day of the month (i.e. 1, 2, 3, etc).

Sub InsertDailyTabs()

    Dim wb As Workbook
    Dim lDay As Long
    Dim d As Long
   
    Application.ScreenUpdating = False

'   Capture active workbook
    Set wb = ActiveWorkbook

'   Find last day of current month
    lDay = Format(Application.WorksheetFunction.EoMonth(Date, 0), "d") + 0
   
'   Loop through days of the month
    For d = 1 To lDay
'       Insert new sheet at end of workbook and name day number
        wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = d
    Next d
   
    Application.ScreenUpdating = True
   
End Sub

Joe4, thanks for responses. Yes, current month would suffice. I will also need to do this for every month after current. If the tabs could be named Feb 01, Feb 02, Feb 03..., that would be great.
Also, G18 is a cell location in every tab that would contain the same date as the tab, but formatted to read Thursday, February 01, 2024. Is it possible for this to be added as part of the code?

Thanks for the help!

 

[Joe4]

Try this variation of the original:

Sub InsertDailyTabs()

    Dim wb As Workbook
    Dim lDay As Long
    Dim d As Long
    
    Application.ScreenUpdating = False

'   Capture active workbook
    Set wb = ActiveWorkbook

'   Find last day of current month
    lDay = Format(Application.WorksheetFunction.EoMonth(Date, 0), "d") + 0
    
'   Loop through days of the month
    For d = 1 To lDay
'       Insert new sheet at end of workbook and name day number
        wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = Format(DateSerial(Year(Date), Month(Date), d), "mmm dd")
        Range("G18") = DateSerial(Year(Date), Month(Date), d)
        Range("G18").NumberFormat = "mmmm dd, yyyy"
    Next d
    
    Application.ScreenUpdating = True
    
End Sub

 

[I am hopeful]

Try this variation of the original:

Sub InsertDailyTabs()

    Dim wb As Workbook
    Dim lDay As Long
    Dim d As Long
   
    Application.ScreenUpdating = False

'   Capture active workbook
    Set wb = ActiveWorkbook

'   Find last day of current month
    lDay = Format(Application.WorksheetFunction.EoMonth(Date, 0), "d") + 0
   
'   Loop through days of the month
    For d = 1 To lDay
'       Insert new sheet at end of workbook and name day number
        wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = Format(DateSerial(Year(Date), Month(Date), d), "mmm dd")
        Range("G18") = DateSerial(Year(Date), Month(Date), d)
        Range("G18").NumberFormat = "mmmm dd, yyyy"
    Next d
   
    Application.ScreenUpdating = True
   
End Sub

Joe4, this works great for current month with tabs labeled as requested. As for my additional request about cell G18, it creates a new worksheet "Sheet1", at the beginning of the workbook with the date in the specified cell, but it does not do this for each daily labeled tab. Any solution to this would be great. Also, how would I replicate for months other than "Current"?

 

[Joe4]

As for my additional request about cell G18, it creates a new worksheet "Sheet1", at the beginning of the workbook with the date in the specified cell, but it does not do this for each daily labeled tab. Any solution to this would be great.

I beg to differ. It renames every tab with your desired data format and populates that date in cell G18 of each sheet.

Here is the first sheet it creates:

and here is the last:

Did you copy and paste ALL of my code, exactly as-is, or did you try typing it or modifying it?

 

[I am hopeful]

I beg to differ. It renames every tab with your desired data format and populates that date in cell G18 of each sheet.

Here is the first sheet it creates:
View attachment 107651

and here is the last:
View attachment 107652

Did you copy and paste ALL of my code, exactly as-is, or did you try typing it or modifying it?

Well, it looks correct on your end. I did a copy/paste of new code you provided. I just tried it again in a new workbook and it does the same thing, with the date of "February 29, 2024" in cell G18 of "Sheet1" only. The rest of the tabs are still there but do not populate cell G18. Hmmmm.

 

[Joe4]

What sheets do you have to start with?
Are you putting this code in a Sheet or ThisWorkbook module, or in a new VBA Module in your workbook?

 

[I am hopeful]

What sheets do you have to start with?
Are you putting this code in a Sheet or ThisWorkbook module, or in a new VBA Module in your workbook?

I open a new book, right click on tab of Sheet1, click on "view code" to open VBA, copy and paste your code to VBA, close VBA, then in that same book i go to "Developer" ribbon and click on macros, then run the selected macro. This may be the wrong process to run a new macro and I am not sure what the module is?