Changing the month in a sheet name

[bearcub]

I have several files that I use every month. I have two sheets which include the current month that I am working on.

For example, I'm starting the November file and I need to rename a couple of these sheets from Sept to Oct (I do commissions so we I am preparing the Oct commissions to be paid at the end of Nov).

I want to change:

Comm_Sept 2019 Brazil Summary

<------------------to-------------------->

Comm_Oct 2019 Brazil Summary

I'm would like to automate this using a macro so I don't have to update it every month (some months I miss changing the name). The current file name I'm using is

Compensation Summary - Nov 2019 - Brazil Accruals for Oct 19

I want to use the word "Oct" in the sheet name (e.g. Comm_Oct 2019 Brazil Summary from Comm_Sept 2019 Brazil Summary).

Is there a macro that change Month in the sheet name to the month in the file name?

Thank you for your help in advance,

Michael

 

[Peter_SSs]

OK, so the sheet names have now moved from 4-digit years to 2-digit years.

IF ..
a) The 2 relevant sheet names start with "Comm_mmm yy" or "mmm yy", and
b) No other sheet names start like that
THEN
this should work for the sheet names for any country/region

Sub ChangeSheetName_v3()
  Dim ws As Worksheet
  Dim sNextMonth As String
  
  Const ShName1 As String = "Comm_??? ## *"
  Const ShName2 As String = "??? ## Comm_*"
  
  For Each ws In Worksheets
    With ws
      Select Case True
        Case .Name Like ShName1
          sNextMonth = Format(DateAdd("m", 1, "1 " & Mid(.Name, 6, 6)), "mmm yy")
          .Name = "Comm_" & sNextMonth & Mid(.Name, 12)
        Case .Name Like ShName2
          sNextMonth = Format(DateAdd("m", 1, "1 " & Left(.Name, 6)), "mmm yy")
          .Name = sNextMonth & Mid(.Name, 7)
      End Select
    End With
  Next ws
End Sub