Create daily tabs for a whole month

I am hopeful

New Member
Joined
Feb 27, 2024
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.
 
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?
I meant to mention that i do select "This Workbook" in the dropdown menu for "Macros in:"
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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?
Yes, you are only that code to one particular sheet, which is why it is behaving like that. You need to put this code in a Standard module, instead of a Sheet module in order to get it to apply to the whole workbook.

See here for instructions on how to insert a new module. This is where you will want to place this code.
 
Upvote 0
Yes, you are only that code to one particular sheet, which is why it is behaving like that. You need to put this code in a Standard module, instead of a Sheet module in order to get it to apply to the whole workbook.

See here for instructions on how to insert a new module. This is where you will want to place this code.
That worked great! Now, I assume I just need to edit part of the code to create another book for a different month?
 
Upvote 0
Here is a version which will prompt the user for the number of the month that you would like to build (so if you wanted to do "June", you would just enter in 6 at the prompt):
VBA Code:
Sub InsertDailyTabs()

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

'   Capture active workbook
    Set wb = ActiveWorkbook

'   Prompt user for month number
    mn = InputBox("Please enter the number of the month you wish to apply this for")

'   Build date
    dt = DateSerial(Year(Date), mn, 1)

'   Find last day of current month
    lDay = Format(Application.WorksheetFunction.EoMonth(dt, 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(dt + d - 1, "mmm dd")
        Range("G18") = dt + d - 1
        Range("G18").NumberFormat = "mmmm dd, yyyy"
    Next d
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Solution
Here is a version which will prompt the user for the number of the month that you would like to build (so if you wanted to do "June", you would just enter in 6 at the prompt):
VBA Code:
Sub InsertDailyTabs()

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

'   Capture active workbook
    Set wb = ActiveWorkbook

'   Prompt user for month number
    mn = InputBox("Please enter the number of the month you wish to apply this for")

'   Build date
    dt = DateSerial(Year(Date), mn, 1)

'   Find last day of current month
    lDay = Format(Application.WorksheetFunction.EoMonth(dt, 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(dt + d - 1, "mmm dd")
        Range("G18") = dt + d - 1
        Range("G18").NumberFormat = "mmmm dd, yyyy"
    Next d
   
    Application.ScreenUpdating = True
   
End Sub

That works great! Thanks for the help Joe4!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top