asked on

Needs Help With Conditional Renaming for Several Tabs

Greetings experts,

I am trying to automate renaming tasks on several tabs. Attached here is an example file with a replica of some tabs that I am working with. For some of my bigger workbooks, there will be over 100 sheets.
example.xlsm

For the 1st tab, the "Allocation" tab, I need to rename it as “Master_CellD28Value” which means that if Cell D28’s value is A123 - FIFO, the tab should be renamed to “Master_A123 - FIFO”.

For the 6th and 7th tab, the "ESD Trf Qty" and "EVNL Trf Qty" tabs, I need it to be renamed like this: The part before “ Trf Qty”_Cell C28’s value. For example, if EVNL Trf Qty tab’s cell C28 value is A123 - LIFO then the tab should be renamed to “EVNL_A123 - LIFO”

The tabs which are named "By Ctrn-EIN", "By Ctrn-EMSB", "By Ctrn-ETH", "By Ctrn-EPC" and "By Ctry-IDC", these need to be renamed to “CellE25Value_CellC28Value”. If Cell E25 Value’s is Canada and Cell C28’s Value is B987 -123 then the tab should be renamed to “Canada_B987 - 123”

As an error proofing method, the last tab, the subset list should be left alone.

I am also planning to embed this script in a command button for a userform so I hope that someone can help to advise me on an “On Error” part of the entire code.

I know that this is a very long and hard request so any help is much appreciated :)

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Noah

ASKER

Thanks, I will give it a try! Can you provide me an example in the case where I want to select multiple tabs to rename? :)

Ryan Chong

it's pretty much the same...

try like:

Sub RenameWorkSheets()
    Dim ws As Worksheet
    
    'Rename Allocation
    Set ws = getWorkSheet("Allocation")
    If Not ws Is Nothing Then
         renameWorkSheet ws, "Master_" & ws.Range("D28").Value
    End If
    
    'Rename ESD Trf Qty
    Set ws = getWorkSheet("ESD Trf Qty")
    If Not ws Is Nothing Then
         renameWorkSheet ws, "ESD_" & ws.Range("C28").Value
    End If
    
    'Your other worksheets
End Sub

Function getWorkSheet(ByVal WorkSheetName As String) As Worksheet
    On Error GoTo EH
    Set getWorkSheet = Worksheets(WorkSheetName)
    Exit Function
EH:
    Set getWorkSheet = Nothing
End Function

Function renameWorkSheet(ByRef ws As Worksheet, ByVal NewName As String) As Boolean
    On Error GoTo EH
    If getWorkSheet(NewName) Is Nothing Then
        ws.Name = NewName
        renameWorkSheet = True
    Else
        'New Worksheet Name already exists
        renameWorkSheet = False
    End If
    Exit Function
EH:
    renameWorkSheet = False
End Function

Open in new window

example_b.xlsm

Noah

ASKER

Is there a better way to extract the info before " Trf Qty" as there could be hundreds of this kind of tabs

Ryan Chong

Is there a better way to extract the info before " Trf Qty" as there could be hundreds of this kind of tabs

sure ... try to implement a looping there, perhaps you can set the prefix into an array, for example.

Noah

ASKER

Hmm I see, I will try it out and get back soon

Ryan Chong

I have put your scenario into my own newly created article:

Rename a list of worksheets using VBA codes with conditions (Ver 1.0)
https://www.experts-exchange.com/articles/33507/Rename-a-list-of-worksheets-using-VBA-codes-with-conditions-Ver-1-0.html

pls see if it's applicable to you...