Global search and replace of text on sheet tabs

Topic

New Reply

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.

Reply | Quote

Replies

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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?

Reply | Quote

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

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Geek,Thanks. Unfortunately it doesn’t work for me. I have attached a sample file.Thanks.

Reply | Quote

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!

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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?

Reply | Quote

Doesn’t seem to work on this one. Again, all I want to do is replace the 12 with, say, a 13.

Reply | Quote

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

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote