how to automatically fill sheet tabs for entire month?

[cooperhall99]

Every month I have to create an excel document that takes inventory of our driver's clock in and out times and other information. To do this I make one sheet with the driver's names, hours, etc. into tables. I then have to duplicate this one sheet 30 or 31 times depending on the number of days in the month. I then go in manually renaming every sheet tab for each day of the month. I start with 01.01.25 and end with 01.31.25. I have to do this for all twelve months and make a new excel document for every location within the 10 states the company is in. As you probably can imagine this is extremely time consuming.

I was wondering if there was any way I could automatically duplicate the first excel sheet 30 or 31 times and have the tabs automatically be named the mm/dd/yy for each sheet. I am not very familiar with a VBE, but am willing to watch a quick Youtube tutorial to figure out the basics. From my little understanding I think it is mostly just copying and pasting. Any advice would be greatly appreciated.

 

[John_w]

If I understand you correctly, try the following macro. You just need to edit the macro where indicated to define the name of your "Inventory" sheet and the folder where the new workbook will be saved.

Option Explicit

Public Sub Create_Current_Month_Workbook()

    Dim copySheet As Worksheet
    Dim saveInFolder
    Dim newWorkbook As Workbook
    Dim m As Long, y As Long
    Dim d As Date
   
    'Worksheet to be copied for each day of the month to the new workbook
   
    Set copySheet = ActiveWorkbook.Worksheets("Inventory")  'Change this
   
    'Folder path where the new workbook will be saved
   
    saveInFolder = "C:\path\to\folder\"                     'Change this
    'saveInFolder = ActiveWorkbook.Path & "\"               'Or same folder as current workbook
   
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
   
    'The new workbook will be created for the current year and month
   
    y = Year(Date)
    m = Month(Date)
   
    Application.ScreenUpdating = False
   
    'Add new workbook with one worksheet
   
    Set newWorkbook = Workbooks.Add(xlWBATWorksheet)

    'Loop every day in this month and copy Inventory sheet to new workbook, named as mm.dd.yyyy
   
    For d = DateSerial(y, m, 1) To DateSerial(y, m + 1, 0)
        copySheet.Copy After:=newWorkbook.Sheets(newWorkbook.Sheets.Count)
        ActiveSheet.Name = Format(d, "mm.dd.yyyy")
    Next

    'Delete the worksheet that was created by default when the new workbook was created

    Application.DisplayAlerts = False
    newWorkbook.Worksheets(1).Delete
    Application.DisplayAlerts = True
   
    'Save new workbook as "<month number> <year>.xlsx".  If the workbook exists it is replaced.
   
    Application.DisplayAlerts = False
    newWorkbook.SaveAs saveInFolder & Format(DateSerial(y, m, 1), "mm yyyy") & ".xlsx", FileFormat:=xlOpenXMLWorkbook
    newWorkbook.Close False
    Application.DisplayAlerts = True
   
    Application.ScreenUpdating = True
   
    MsgBox "Done"
   
End Sub

 

[cooperhall99]

If I understand you correctly, try following macro. You just need to edit the macro where indicated to define the name of your "Inventory" sheet and the folder where the new workbook will be saved.

Option Explicit

Public Sub Create_Current_Month_Workbook()

    Dim copySheet As Worksheet
    Dim saveInFolder
    Dim newWorkbook As Workbook
    Dim m As Long, y As Long
    Dim d As Date
   
    'Worksheet to be copied for each day of the month to the new workbook
   
    Set copySheet = ActiveWorkbook.Worksheets("Inventory")  'Change this
   
    'Folder path where the new workbook will be saved
   
    saveInFolder = "C:\path\to\folder\"                     'Change this
    'saveInFolder = ActiveWorkbook.Path & "\"               'Or same folder as current workbook
   
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
   
    'The new workbook will be created for the current year and month
   
    y = Year(Date)
    m = Month(Date)
   
    Application.ScreenUpdating = False
   
    'Add new workbook with one worksheet
   
    Set newWorkbook = Workbooks.Add(xlWBATWorksheet)

    'Loop every day in this month and copy Inventory sheet to new workbook, named as mm.dd.yyyy
   
    For d = DateSerial(y, m, 1) To DateSerial(y, m + 1, 0)
        copySheet.Copy After:=newWorkbook.Sheets(newWorkbook.Sheets.Count)
        ActiveSheet.Name = Format(d, "mm.dd.yyyy")
    Next

    'Delete the worksheet that was created by default when the new workbook was created

    Application.DisplayAlerts = False
    newWorkbook.Worksheets(1).Delete
    Application.DisplayAlerts = True
   
    'Save new workbook as "<month number> <year>.xlsx".  If the workbook exists it is replaced.
   
    Application.DisplayAlerts = False
    newWorkbook.SaveAs saveInFolder & Format(DateSerial(y, m, 1), "mm yyyy") & ".xlsx", FileFormat:=xlOpenXMLWorkbook
    newWorkbook.Close False
    Application.DisplayAlerts = True
   
    Application.ScreenUpdating = True
   
    MsgBox "Done"
   
End Sub

Thank you very much for the reply. I have a few questions. Do I type out everything you wrote or can I copy and paste it? Also the text that appears in green is it steps or do I also type that in?

 

[John_w]

You can copy the whole code by clicking this icon at the top-right of my post:

and paste it into a standard (regular) module, as shown by How to Add Macro Code to Excel Workbook.

The green text are comments (preceded by the apostrophe character) which explain the meaning of some lines and what the code is doing. They are part of the code, but not executed when the code is run.

 

[cooperhall99]

You can copy the whole code by clicking this icon at the top-right of my post:

View attachment 120901

and paste it into a standard (regular) module, as shown by How to Add Macro Code to Excel Workbook.

The green text are comments (preceded by the apostrophe character) which explain the meaning of some lines and what the code is doing. They are part of the code, but not executed when the code is run.

Thank you so much!!!

 

[cooperhall99]

Thank you so much!!!

It worked perfectly but how do I change it for other months besides january?

 

[John_w]

Do you want it to do all months in the year?