• 0

How To Auto Fill Dates In Sheets Tab ?

  • 0

I like to name each sheet with dates ,Like 01.01.2010 and next sheet with 01,02.2010 and so on ….. I would like to know if there is way to do it with some kind of auto fill than individually entering each dates .


1 Answer

  1. Hi,

    You haven’t said if this is a one-time requirement or a regular requirement.

    I have written a short macro which assumes that this is a one-time requirement, and therefore there are few frills – for example, enter text that Excel does not recognize as a date, and the macro exits – no helpful messages or offers to try again.

    Right-click on the name tab of a worksheet in your workbook.
    Select ‘View Code’
    In the large Visual Basic window that opens, enter this:

    Sub AddDatedWS()
    Dim strStartDt As String
    Dim strEndDt As String
    Dim dtStart As Date
    Dim dtEnd As Date
    Dim wsNew As Worksheet
    Dim n As Double
    'get start date
    strStartDt = InputBox("Enter start date", "Create dated worksheets")
    If Not IsDate(strStartDt) Then Exit Sub
    'get end date
    strEndDt = InputBox("Enter end date", "Create dated worksheets")
    If Not IsDate(strStartDt) Then Exit Sub
    'convert text to Excel's date format
    dtStart = CDate(strStartDt)
    dtEnd = CDate(strEndDt)
    'test if start date equal to or later than end date
    If dtStart >= dtEnd Then Exit Sub
    'confirm number of sheets
    If MsgBox("Create " & dtEnd - dtStart + 1 & " worksheets", vbOKCancel) = _
                vbCancel Then Exit Sub
    For n = dtStart To dtEnd
        'create a new worksheet
        Set wsNew = ActiveWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))
        'name it with a date (date text can't contain : \ / ? * [ or ])
        wsNew.Name = Format(n, "")
    Next n
    End Sub

    Place you cursor in the macro – on the line “Sub AddDatedWS ()” will do.
    Click the f5 function key.
    You will get two input boxes to allow you to enter start and end dates.
    Enter dates in a format that Windows uses in your country, and Excel should recognize them. Use month abbreviations to avoid any risk of the order day-month / month-day being confused
    After entering the end date, the macro asks if you want to create that number of worksheets – just in case you made a mistake and Excel is going to try and create 20000 worksheets !!!
    Click OK and it then creates the required number of worksheets.
    Click Cancel and the macro exits.

    If you are going to use this often, then you need to decide where to place the macro and you will have to have a way to tell the macro which workbook to use. Also it would be nice to add some more user friendly steps if mistakes are made.


    • 0