Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Changing the month in a sheet name
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2005
    Posts
    597
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Changing the month in a sheet name

    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

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,400
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Changing the month in a sheet name

    Quote Originally Posted by bearcub View Post
    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?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    Board Regular
    Join Date
    May 2005
    Posts
    597
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing the month in a sheet name

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

  4. #4
    Board Regular
    Join Date
    May 2005
    Posts
    597
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing the month in a sheet name

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

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,400
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Changing the month in a sheet name

    Quote Originally Posted by bearcub View Post
    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.

    Quote Originally Posted by bearcub View Post
    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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    Board Regular
    Join Date
    May 2005
    Posts
    597
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing the month in a sheet name

    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

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,400
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Changing the month in a sheet name

    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)

    Code:
    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    Board Regular
    Join Date
    May 2005
    Posts
    597
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing the month in a sheet name

    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.

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,400
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Changing the month in a sheet name

    Quote Originally Posted by bearcub View Post
    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 ...

    Code:
    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?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    Board Regular
    Join Date
    May 2005
    Posts
    597
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing the month in a sheet name

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •