Add consecutive year_month sheets

[Vincent88]

Hi Everybody, Need help to modify the code to create consecutive month sheets. ( if 2021_07 exists, then 2021_08 and so on).

Sub AddMonthWkst()
Dim ws As Worksheet
Dim wsM As Worksheet
Dim strName As String
Dim bCheck As Boolean

On Error Resume Next
Set wsM = Sheets("202106")
strName = Format(Date, "yyyymm")
'bCheck = Len(Sheets(strName).Name) > 0

If bCheck = False Then
'add new sheet after Instructions
    wsM.Copy Before:=Sheets(1)
    ActiveSheet.Name = strName
End If

Set wsM = Nothing
End Sub

 

[Phuoc]

Try this:

Sub AddMonthWkst()
Dim ws As Worksheet
Dim wsM As Worksheet
Dim strName As String
Dim bCheck As Boolean

On Error Resume Next
Set wsM = Sheets("202106")
strName = Format(Date, "yyyymm")
bCheck = Evaluate("IsError('" & strName & "'!A1)")
If bCheck = True Then
'add new sheet after Instructions
    wsM.Copy Before:=Sheets(1)
    ActiveSheet.Name = strName
End If

Set wsM = Nothing
End Sub

 

[Vincent88]

Hi Phuoc,
It adds a sheet of current month only, No sheets are added after further run . (Mine can still add sheet but in sheetname 202106 (2).

 

[Phuoc]

Hi;

Try this

Sub AddNextMonth()
Dim ws As Worksheet
Dim wsM As Worksheet
Dim strName As String
Dim maxName As Long

For Each ws In ThisWorkbook.Sheets
    If IsNumeric(ws.Name) Then
        If CLng(ws.Name) > maxName Then maxName = CLng(ws.Name)
    End If
Next
If maxName > 0 Then
    strName = Format(DateAdd("m", 1, DateSerial(Int(maxName / 100), maxName Mod 100, 1)), "yyyymm")
    Set wsM = Sheets(CStr(maxName))
    wsM.Copy Before:=Sheets(1)
    ActiveSheet.Name = strName
End If

Set wsM = Nothing
End Sub