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]

The current file name I'm using is

Compensation Summary - Nov 2019 - Brazil Accruals for Oct 19

Just checking that the file name you have given is correct in that you are using the full 4-digit year in one place but only 2 digits in the other place?

 

[bearcub]

Sorry for the confusion. The 3 digit version would be easier to use, please use 3 digits. Sept should be Sep.

 

[bearcub]

Sorry, I was referring to the months but Peter was asking about the year. Please use the 2 digit year.

 

[Peter_SSs]

Please use the 2 digit year.

You say "use the 2 digit year" but where would I be using that as your sheet names have 4 digit years.

I want to change:

Comm_Sept 2019 Brazil Summary

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

Comm_Oct 2019 Brazil Summary

What I was actually asking in my previous post was not "which to use" but whether your file name contained both 4 and 2 digit years where I showed in red.

 

[bearcub]

Hi Peter,

You're right. They lack consistency. I just rename the file monthly without paying attention to the year.

"Compensation Summary - Nov 2019 - Brazil Accruals for Oct 19" - I should have written Nov 19 instead. I'll do this going forward.

Would it make a difference in the macro?

I was thinking that the macro would extract the month from the file name and then insert the month name into a preset string. I have other files in the same format for International and US. This file name I used was for the Brazil accruals.
I can start using 2 digit years going forward if the macro was dependent on it.

Thank you, Michael

 

[Peter_SSs]

The year is important since if the current month is December then when we move to the next month (January) the year also has to change. I was therefore trying to be certain whether I was looking for 2 digits in the file name or 4 digits.

However, I have changed approach and am now just looking at the sheet names in the workbook and any that fit the pattern "Comm_??? #### * Summary" (where ? stands for a single character, # stands for a single digit & * stands for any number of characters) get their names updated. Give it a try in a copy of your workbook. If you run the macro several times you should see that the sheet names gradually advance through the months (& years if you progress past December)

Sub ChangeSheetName()
  Dim ws As Worksheet
  Dim sNextMonth As String
  
  Const ShName As String = "Comm_??? #### * Summary"
  
  For Each ws In Worksheets
    If ws.Name Like ShName Then
      With ws
        sNextMonth = Format(DateAdd("m", 1, "1 " & Mid(.Name, 6, 8)), "mmm yyyy")
        .Name = Left(.Name, 5) & sNextMonth & Mid(.Name, 14)
      End With
    End If
  Next ws
End Sub

 

[bearcub]

Thank you Peter!

If I just wanted to run this macro on the active sheet only would I remove the For Each statement and replace it With ActiveSheet (or something like this).

Sorry I didn't explain everything. I do have supporting documents that don't include the month name.

I would want this macro for a couple of sheets in each workbook (I know I would have to tweak the string wording).

Thank you again.

 

[Peter_SSs]

If I just wanted to run this macro on the active sheet only ..

I would want this macro for a couple of sheets in each workbook

If you just want to run on the active sheet you should just need this ...

Sub ChangeSheetName_v2()
  Dim sNextMonth As String

  With ActiveSheet
    sNextMonth = Format(DateAdd("m", 1, "1 " & Mid(.Name, 6, 8)), "mmm yyyy")
    .Name = Left(.Name, 5) & sNextMonth & Mid(.Name, 14)
  End With
End Sub

.. However, if you have one or more other sheets that do need changing in some way, why not give us some more specific information about their names & what needs changing and we may be able to get the one macro to do both/all at the one time?

 

[bearcub]

that is a good idea. However, each month I have different sheet names due to various payroll corrections I have to do.

I have 2 sheet names that don't really change other than the month caption:

Comm_Oct 19 US Summary - this is a summary from which payroll pays for commissions

Oct 19 Comm_Xactly US - is from our commissions system.

The other sheets vary and don't have month names.

This are the 2 sheets in one file, then I have 2 sheets for Europe and Asia Pacific, then another one for Brazil.

Same 2 sheets but with a different payroll center and month names.

Does this help?

Michael