Changing Tab Names in Excel with VB

[Sareimer]

if I have 20 excel sheets with 'MR' in the title....is there a way to mass change every title to 'MRS'

 

[SkipVought]

Hi,

dim ws as worksheet, i as integer, b as string

for each ws in worksheets
  with ws
    if .name like "*MR*" then
      for i = 1 to len(.name)-1 
        if mid(.name, i,2)="MR" and not mid(.name, i,3)="MRS" then
          .name = left(.name,i+1) & "S" & mid(.name, i+2)
          exit for
        end if
      next
    end if
  end with
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Loomah]

this is just because i'm having another one of those "i'm really bored" moments!

as above but ruduces the looping. assumes sheet names are individually in the same case and checks for upper or lower case on each sheet. this is necessary as i have a feeling substitute is case sensitive.

Sub zero()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If InStr(1, UCase(ws.Name), "MR") > 0 And _
        (InStr(1, UCase(ws.Name), "MR") <> InStr(1, UCase(ws.Name), "MRS")) Then
            If UCase(ws.Name) = ws.Name Then
                ws.Name = WorksheetFunction.Substitute(ws.Name, "MR", "MRS")
            Else
                ws.Name = WorksheetFunction.Substitute(ws.Name, "mr", "mrs")
            End If
    End If
Next
End Sub

just sillines!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​