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.

 

[I am hopeful]

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:"

 

[Joe4]

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.

Insert and run VBA macros in Excel - step-by-step guide

This is a short step-by-step tutorial for beginners showing how to add VBA code (Visual Basic for Applications code) to your Excel workbook and run this macro to solve your spreadsheet tasks.

www.ablebits.com

 

[I am hopeful]

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.

Insert and run VBA macros in Excel - step-by-step guide

This is a short step-by-step tutorial for beginners showing how to add VBA code (Visual Basic for Applications code) to your Excel workbook and run this macro to solve your spreadsheet tasks.

www.ablebits.com

That worked great! Now, I assume I just need to edit part of the code to create another book for a different month?

 

[Joe4]

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):

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

 

[I am hopeful]

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):

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!

 

[Joe4]

You are welcome.