How to add new sheet with sheet name is the day of month

[nhnn1986]

Hi all

I have workbook with some sheet(MAIN, BC1,BC2, PL1,PL2,....,PL30) and now Iwant to have a macro that

when I type 201810 in sheet(Main).range(A1) then run macro
workbook add 31 newsheets with sheetname is the day of month like: 20181001, 20181002,.....,20181031

Next month, I type 201811 in sheet(Main).range(A1) then run macro
workbook first delete 31 sheets with sheetname of old month like: 20181001, 20181002,.....,20181031
And then add 30 newsheets with sheetname is the day of month like: 20181101, 20181102,.....,20181130

Please help me to do that.
Thanks in advanced

 

[Joe4]

Try this:

Sub MyMacro()

    Dim startDate As Date
    Dim endDate As Date
    Dim d As Long
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False

    Sheets("Main").Activate
    
'   Get start date from year/month in cell A1
    On Error GoTo err_chk
    startDate = DateSerial(Left(Range("A1"), 4), Mid(Range("A1"), 5), 1)
    On Error GoTo 0
    
'   Find end date
    endDate = DateSerial(Year(startDate), Month(startDate) + 1, 0)

'   Delete old month dates
    For Each ws In Worksheets
        If Len(ws.Name) = 8 And Left(ws.Name, 2) = "20" Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    Next ws
   
'   Loop through all days of month
    For d = startDate To endDate
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = Format(d, "yyyymmdd")
    Next d
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
    Exit Sub
    
err_chk:
'   Return error message if invalid date value entered in cell A1
    MsgBox Range("A1") & " is not a valid year/month value!", vbOKOnly, "ERROR!"
    
End Sub

 

[nhnn1986]

Code worked perfect.

Many thanks @Joe4

 

[Joe4]

You are welcome.
Glad I was able to help!