VBA code to format Year(NOW) to YY

[dannyh]

Hi I am using the following code to rename a new sheet that gets created when the workbook is opened.

Can anyone help me add code to format the Year as only 2 digits not 4, thanks.

Dim iMonth As Integer, sMonth As String
Dim sYear As String

iYear = Year(Now)
iMonth = Month(Now) - 1
Select Case iMonth
Case 1
sMonth = "Jan"
Case 2
sMonth = "Feb"
Case 3
sMonth = "Mar"
Case 4
sMonth = "Apr"
Case 5
sMonth = "May"
Case 6
sMonth = "Jun"
Case 7
sMonth = "Jul"
Case 8
sMonth = "Aug"
Case 9
sMonth = "Sep"
Case 10
sMonth = "Oct"
Case 11
sMonth = "Nov"
Case 10
sMonth = "Dec"
End Select
Worksheets(1).Name = sMonth & "_" & iYear

Thanks Dan

 

[jasonb75]

your method still creates the sheet just doesn't rename it if the sheet name exists.

Oops, my bad, trying to keep it minimal I cut it a bit too short

Dim wsName As String
wsName = Format(DateAdd("m", -1, Date), "mmm_yy")
On Error Resume Next
    If Worksheets(wsName).Name = wsName Then Exit Sub
On Error GoTo 0
    ThisWorkbook.Sheets.Add(After:=.Sheets("IC DATA")).Name = wsName