Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Global search and replace of text on sheet tabs

    I have a file with many worksheets. Is there a way to search and replace certein text (say a date) on the sheet tabs?Thanks in advance.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,903
    Thanks
    190
    Thanked 719 Times in 655 Posts
    J.L.,

    I think you'll have to use code to accomplish this task. Here's a sample routine to accomplish the task and a sample workbook.
    BTW: I couldn't get a tab to accept the / character for dates so I used the - instead.
    Code:
    Option Explicit
    
    Sub ChgShtTabNames()
    
       Dim zNewYr As String
       Dim sht    As Worksheet
       
       zNewYr = InputBox("Enter the 4 digit Year to be used.", "Change Sheet Tab Years", Year(Now()))
       
       For Each sht In ThisWorkbook.Sheets
          sht.Name = Left(sht.Name, Len(sht.Name) - 4) & zNewYr
       Next sht
       
    End Sub
    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Geek. Actually, I should have been clearer in my description: the sheet tabs in question contain both text and numbers (a year). For example, a typical sheet tab would be "Master file Feb 12", or "Detail file Jun 12". What I would like to do is search for the 12 and replace it with a 13. Make sense?

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,903
    Thanks
    190
    Thanked 719 Times in 655 Posts
    J.L.

    This should do it.
    Code:
    Option Explicit
    
    Sub ChgShtTabNames()
    
       Dim zNewYr As String
       Dim sht    As Worksheet
       
       zNewYr = InputBox("Enter the 2 digit Year to be used.", "Change Sheet Tab Years", Right(Year(Now()), 2))
       
       For Each sht In ThisWorkbook.Sheets
          sht.Name = Left(sht.Name, Len(sht.Name) - 2) & zNewYr
       Next sht
       
    End Sub
    Last edited by RetiredGeek; 2013-01-21 at 11:21.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Geek,Thanks. Unfortunately it doesn't work for me. I have attached a sample file.Thanks.
    Attached Files Attached Files

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,903
    Thanks
    190
    Thanked 719 Times in 655 Posts
    J.L.

    Worked fine on your file once I put the code in. From the Worksheet page just press Alt+F8 select the macro then click Run.

    Make sure you put the file in a Trusted Location or set Macro Security to Low!

    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  8. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Not sure what I have done, but none of my macros are showing up in "This Workbook", only "Personal.xlsc". Any ideas how to get them back?

  9. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Doesn't seem to work on this one. Again, all I want to do is replace the 12 with, say, a 13.
    Attached Files Attached Files

  10. #9
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,049
    Thanks
    39
    Thanked 182 Times in 169 Posts
    a typical sheet tab would be "Master file Feb 12", or "Detail file Jun 12".[
    JK,
    I placed RG's code in a new workbook with several sheet names in the format described. Routine worked flawlessly just as you requested whether I placed it in a standard module, in a worksheet module, or in a userform click event subroutine. His code is sound as usual. The problem lies in that there are additional characters after the day that you did not mention (Ex: Jan 12 Confirmation in your upload). RG's code trims the last 2 digitsoff the right assuming that the "12" were the last 2 digits as described. To replace the day in the middle of the string will require the use of the Instr, Mid, and Len functions or something similar.

    HTH,
    Maud
    Last edited by Maudibe; 2013-01-21 at 16:21. Reason: If I could only speak proper English

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,903
    Thanks
    190
    Thanked 719 Times in 655 Posts
    J.L.,

    Ok here's the code to match the final specification.
    Please note two things here:
    1. You need to specify your requirements exactly to receive the help you need.
    2. Look back over the versions of the code in this thread and you can begin to learn how to do this yourself.
    Code:
    Option Explicit
    
    Sub ChgShtTabNames()
    
       Dim zNewYr      As String
       Dim sht         As Worksheet
       Dim iLoc        As Integer
       Dim zNewShtName As String
    
       zNewYr = InputBox("Enter the 2 digit Year to be used.", "Change Sheet Tab Years", Right(Year(Now()), 2))
       
       For Each sht In ThisWorkbook.Sheets
          iLoc = InStr(sht.Name, " ") + 1
          zNewShtName = sht.Name
          Mid(zNewShtName, iLoc, 2) = zNewYr
          sht.Name = zNewShtName
       Next sht
       
    End Sub
    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  12. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Geek and Maud,Thanks for your help; sorry for the confusion/lack of better explanation on my part! Will try and do better in future.Thanks again.

  13. #12
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 167 Times in 163 Posts
    Hi jkirk

    ..and just to be clear, one of your uploads was a .xlsx file.
    These .xlsx files cannot contain macros.

    You must use the extension .xlsm for macros, or, as I always prefer, a .xlsb extension.
    The .xlsb extension stands for Binary type, which means smaller file sizes, but these can also have macros in them.
    Smaller file sizes are always great for faster loading, less network traffic etc etc.

    zeddy

  14. #13
    New Lounger
    Join Date
    Dec 2009
    Location
    Gordonsville, VA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi zeddy,

    Technically, you can't SAVE macros in .xlsx files. You can, however, USE them. If the editing is a one-time conversion, then deleting the macros before saving the updated .xlsx file will work.

    Cheers,

    Mitch

  15. #14
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 167 Times in 163 Posts
    Hi Anklebuster

    You are correct. You can't SAVE macros in .xlsx files.
    So when I said one of the uploaded files was a .xlsx file, I was correct in saying it couldn't contain macros.

    Technically, as you correctly say, although you can put macros into an opened .xlsx file and use the macros, as soon as you save the file (with .xlsx extension) you will be warned and the macros will be deleted for you in the saved version of the .xlsx file, but will STILL be available in the open .xlsx file (until you close it).
    So technically you don't even need to delete the macros before saving, because this will be done automatically for you.

    zeddy

Posting Permissions

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