Auto creation of day-wise tabs(work-sheets) after updation date in specific cell

Dear Excel Experts,

We have a Excel file, which we have using as template for all month, however, currently we are preparing day-wise tabs(work-sheets) into a workbook(attached) manually. For which we are looking automated way/process. i.e. after updating 1st day of the month in cell C1, it should create day-wise tabs(work-sheets) in a workbook

For example after updating 1-May-18 in cell C1

day-wise tabs(work-sheets) should be created as follows;
01-May, 02-May, 03-May, 04-May, 05-May, 06-May, 07-May, 08-May,09-May, 10-May, 11-May, 12-May, 13-May, 14-May, 15-May, 16-May, 17-May, 18-May, 19-May, 20-May, 21-May, 22-May, 23-May, 24-May, 25-May, 26-May, 27-May, 28-May, 29-May, 30-May, 31-May

and all worksheets C1 cell with tab(work-sheet) name

Please ref. attached workbook for details.

Thanks in advance.

Kravindra

You could use this code, right click in the bottom on the sheet name, choose view code and paste the code on the right bigger window, then click on the code and press play on the top:

Sub macro1()
   Dim m As Integer, myMonths As String
   Dim lastDay As Integer, d As Integer
   Dim mName As String
   
   Application.ScreenUpdating = False
   
   myMonths = "JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC"
   With ActiveWorkbook
      m = InStr(myMonths, Right(.ActiveSheet.Name, 3)) \ 4 + 1
      lastDay = Day(DateSerial(Year(Now), m + 1, 1) - 1)
      mName = Right(.Sheets(1).Name, 3)
      
      For d = 2 To lastDay
         .Sheets(1).Copy After:=.Sheets(.Sheets.Count)
         .ActiveSheet.Name = Format(d, "00-") & mName
         .ActiveSheet.range("c1").formula = "='" & Format(d - 1, "00-") & mName & "'!c1+1"
      Next d
   End With
   Application.ScreenUpdating = True
End Sub

Regards,
Antonio

antoka05,

Thanks for your prompt response and great macro code.

It's working, just having a small error.

After running macro, all previous sheets(tabs) should be deleted and new sheets(tabs) should be created considering C1 in 1st sheet(tab)

Please help to fix it.

Thanks in advance.

Kravindra

Hi !

Smarter is to create a template workbook with only one empty worksheet and at its opening
asking for month / year in order to create all needed worksheets …

You could use this codde:

Sub macro1()
   Dim m As Integer, myMonths As String
   Dim lastDay As Integer, d As Integer
   Dim mName As String
   
   Application.ScreenUpdating = False
   
   myMonths = "JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC"
   With ActiveWorkbook
      m = Month(.Sheets(1).Range("c1"))
      
      Application.DisplayAlerts = False
      Do While .Sheets.Count > 1
         .Sheets(2).Delete
         DoEvents
      Loop
      Application.DisplayAlerts = True
      
      lastDay = Day(DateSerial(Year(Now), m + 1, 1) - 1)
      mName = Mid(myMonths, 4 * m - 3, 3)
      
      .Sheets(1).Name = "01-" & mName
      For d = 2 To lastDay
         .Sheets(1).Copy After:=.Sheets(.Sheets.Count)
         .ActiveSheet.Name = Format(d, "00-") & mName
         .ActiveSheet.Range("c1").Formula = "='" & Format(d - 1, "00-") & mName & "'!c1+1"
      Next d
   End With
   Application.ScreenUpdating = True
End Sub

Regards,
Antonio

Perfect Antonio Sir,
Hats off to you, you are really charm !!!
Kravindra