How To Auto Fill Dates In Sheets Tab ?

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, "dd.mm.yy")
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.

Regards