I know how to rename the worksheet
Dim i As Integer
For i = 1 To ActiveWorkbook.Sheets.Count
    Sheets(i).Name = "Result" & i
Next

but don't know how to name worksheet by Month or year. Pls advise. thx.

Is this what you need?:

Dim i As Integer
For i = 1 To ActiveWorkbook.Sheets.Count
   Sheets(i).Name = MonthName(i)
Next

Use the immediate window to see this one:

Sub ShtName()
Dim i, o As Integer
For i = 1 To 24
    If i < 13 Then
        Debug.Print MonthName(i) & "-" & Year(Now)
    Else
        o = i - 12
        Debug.Print MonthName(o) & "-" & Year(Now) + 1
    End If
Next
End Sub

Just change the code to use it with sheets - max 24