Convert NowMonth MonthNow to double digits mm

ChrisOK · #1 04-20-2021, 10:04 AM

How do I convert / format the month name of a SHEET / TAB to be double digits rather than single?
It currently produces: "2021-3" but need "2021-03"

Code:

Sub InsertNewWorksheetAndName()

    nowMonth = Month(Now) - 1
    nowYear = Year(Now)

    ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
    ActiveWorkbook.Sheets(Worksheets.Count).Name = nowYear & "-" & nowMonth

End Sub

macropod · #2 04-20-2021, 03:37 PM

Code:

Sub InsertNewWorksheetAndName()
Application.ScreenUpdating = False
Dim nowMonth As Long, nowYear As Long
nowMonth = Month(Now) - 1: nowYear = Year(Now)
With ActiveWorkbook
  .Sheets.Add After:=Worksheets(.Worksheets.Count)
  .Sheets(.Worksheets.Count).Name = nowYear & "-" & Format(nowMonth, "00")
End With
Application.ScreenUpdating = True
End Sub

ChrisOK · #3 04-20-2021, 05:00 PM

@macropod Thank you greatly! That works!
(of course it does) still in awe

macropod · #4 04-20-2021, 05:21 PM

Even simpler:

Code:

Sub InsertNewWorksheetAndName()
Application.ScreenUpdating = False
With ActiveWorkbook
  .Sheets.Add After:=Worksheets(.Worksheets.Count)
  .Sheets(.Worksheets.Count).Name = Format(DateAdd("M", -1, Now()), "YYYY-MM")
End With
Application.ScreenUpdating = True
End Sub

p45cal · #5 04-26-2021, 03:47 AM

…and shorter:

Code:

Sub InsertNewWorksheetAndName()
Application.ScreenUpdating = False
With ActiveWorkbook
  .Sheets.Add(After:=.Worksheets(.Worksheets.Count)).Name = Format(DateAdd("M", -1, Now()), "YYYY-MM")
End With
Application.ScreenUpdating = True
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find last three digits after . ( including zero)	LearnerExcel	Excel	4	02-08-2018 10:49 AM
Having number's digits together	mohsen.amiri	Word	0	06-23-2017 01:20 AM
Need help calculating frequency of digits	laucn	Excel	2	06-08-2015 07:50 AM
Separate the digits into 3 combinations	Jasa P	Word VBA	1	08-19-2012 11:04 PM
Problems merging in last 4 digits of an account higher than 16 digits	Glynda	Mail Merge	1	04-08-2011 12:17 AM