Generating next worksheet tab name

[Paul at GTS]

Have a work book with 12 worksheets - 1 per month called :
Jan 19, Feb 19,Mar 19 etc

I am trying to get a formula to read the next sheet in the series. I have it working fine when i manually input the next sheet number but would like say, Jan 19 to be able to calculate the next sheet name as Feb 19

Sheets are named exactly as shown 3 Letters, Space, 2 Numbers.

Any ideas ?

Thanks

 

[Joe4]

Try this:

Sub CreateNextSheet()

    Dim lastName As String
    Dim nextName As String

'   Get name of last sheet
    lastName = Sheets(Sheets.Count).Name
    
'   Build name of next month
    nextName = Format(DateAdd("m", 1, DateValue(Left(lastName, 4) & "1, 20" & Right(lastName, 2))), "mmm yy")
    
'   Add new sheet
    Sheets.Add After:=Sheets(Sheets.Count)
    
'   Name sheet
    ActiveSheet.Name = nextName
    
End Sub

 

[Paul at GTS]

Thanks Joe4, I'll give it a try