Rename part of multiple sheets if it meets my critera

vlacombe

New Member
Joined
Oct 4, 2019
Messages
31
I already have a script that does most of the mumbo jumbo of what I need, however at the very end of that script, I want to add the part to rename part of some of my sheets to something else

I have multiple template sheets and a master sheet within a workbook, those need to remain untouched. Here are the criteras

For all the sheets that are named Type1-xxxxxx "Type1" need to change to "PrixMax" while everything after Type1 remains
For all the sheets that are named Type2-xxxxxx "Type2" need to change to "6po" while everything after Type2 remains
For all the sheets that are named Type3-xxxxxx "Type3" need to change to "Quote" while everything after Type3 remains

Script needs not to error out nor pop up any message.
It could be possible that the script has to execute while absolutely no Type1, 2 or 3 are existant and I would want to avoid any error or pop ups on that as well.

Can anyone suggest me a script?
For general information, I am running the latest Office 365 excel version

Thank you for your assistance
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Maybe:
Code:
Sub ChangeSomeSheetNames()
Dim whichSheets As Variant, changeTo As Variant, Sht As Worksheet, i As Long
whichSheets = Array("Type1", "Type2", "Type3")
changeTo = Array("PrixMax", "6po", "Quote")
Application.ScreenUpdating = False
For Each Sht In Worksheets
    For i = LBound(whichSheets) To UBound(whichSheets)
        If Sht.Name Like "*" & whichSheets(i) & "*" Then
            Sht.Name = Replace(Sht.Name, whichSheets(i), changeTo(i))
        End If
    Next i
Next Sht
Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top