SOLVED
Home

Needs Help With Conditional Renaming for Several Tabs

%3CLINGO-SUB%20id%3D%22lingo-sub-386514%22%20slang%3D%22en-US%22%3ENeeds%20Help%20With%20Conditional%20Renaming%20for%20Several%20Tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386514%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22content%20wysiwyg-content%22%3EGreetings%20experts%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20trying%20to%20automate%20renaming%20tasks%20on%20several%20tabs.%20Attached%20here%20is%20an%20example%20file%20with%20a%20replica%20of%20some%20tabs%20that%20I%20am%20working%20with.%20For%20some%20of%20my%20bigger%20workbooks%2C%20there%20will%20be%20over%20100%20sheets.%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20the%201st%20tab%2C%20the%20%22Allocation%22%20tab%2C%20I%20need%20to%20rename%20it%20as%20%E2%80%9CMaster_CellD28Value%E2%80%9D%20which%20means%20that%20if%20Cell%20D28%E2%80%99s%20value%20is%20A123%20-%20FIFO%2C%20the%20tab%20should%20be%20renamed%20to%20%E2%80%9CMaster_A123%20-%20FIFO%E2%80%9D.%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20the%206th%20and%207th%20tab%2C%20the%20%22ESD%20Trf%20Qty%22%20and%20%22EVNL%20Trf%20Qty%22%20tabs%2C%20I%20need%20it%20to%20be%20renamed%20like%20this%3A%20The%20part%20before%20%E2%80%9C%20Trf%20Qty%E2%80%9D_Cell%20C28%E2%80%99s%20value.%20For%20example%2C%20if%20EVNL%20Trf%20Qty%20tab%E2%80%99s%20cell%20C28%20value%20is%20A123%20-%20LIFO%20then%20the%20tab%20should%20be%20renamed%20to%20%E2%80%9CEVNL_A123%20-%20LIFO%E2%80%9D%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20tabs%20which%20are%20named%20%22By%20Ctrn-EIN%22%2C%20%22By%20Ctrn-EMSB%22%2C%20%22By%20Ctrn-ETH%22%2C%20%22By%20Ctrn-EPC%22%20and%20%22By%20Ctry-IDC%22%2C%20these%20need%20to%20be%20renamed%20to%20%E2%80%9CCellE25Value_CellC28Value%E2%80%9D.%20If%20Cell%20E25%20Value%E2%80%99s%20is%20Canada%20and%20Cell%20C28%E2%80%99s%20Value%20is%20B987%20-123%20then%20the%20tab%20should%20be%20renamed%20to%20%E2%80%9CCanada_B987%20-%20123%E2%80%9D%3CBR%20%2F%3E%3CBR%20%2F%3EAs%20an%20error%20proofing%20method%2C%20the%20last%20tab%2C%20the%20subset%20list%20should%20be%20left%20alone.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20also%20planning%20to%20embed%20this%20script%20in%20a%20command%20button%20for%20a%20userform%20so%20I%20hope%20that%20someone%20can%20help%20to%20advise%20me%20on%20an%20%E2%80%9COn%20Error%E2%80%9D%20part%20of%20the%20entire%20code.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20know%20that%20this%20is%20a%20very%20long%20and%20hard%20request%20so%20any%20help%20is%20much%20appreciated%20%3A)%3C%2FDIV%3E%3CDIV%20class%3D%22rating%20disabled%22%3E%3CDIV%20class%3D%22member-rating%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-386514%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389852%22%20slang%3D%22en-US%22%3ERe%3A%20Needs%20Help%20With%20Conditional%20Renaming%20for%20Several%20Tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389852%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%20I%20have%20figured%20it%20out%20myself.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20full%20code%3A%3C%2FP%3E%3CPRE%3ESub%20RenameWorkSheets()%0A%20%20%20%20Dim%20ws%20As%20Worksheet%0A%20%20%20%20Dim%20sh%20As%20Worksheet%0A%20%20%20%20%0A%20%20%20%20'Rename%20Allocation%0A%20%20%20%20Set%20ws%20%3D%20getWorkSheet(%22Allocation%22)%0A%20%20%20%20If%20Not%20ws%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20renameWorkSheet%20ws%2C%20%22Master_%22%20%26amp%3B%20ws.Range(%22D28%22).Value%0A%20%20%20%20End%20If%0A%20%20%20%20%0A%20%20%20%20'Other%20worksheets%0A%20%20%20%20For%20Each%20sh%20In%20Worksheets%0A%20%20%20%20If%20sh.Name%20Like%20%22*Trf%20Qty%22%20Then%0A%20%20%20%20sh.Name%20%3D%20Split(sh.Name%2C%20%22%20%22)(0)%20%26amp%3B%20%22_%22%20%26amp%3B%20sh.%5Bc28%5D%0A%20%20%20%20ElseIf%20sh.Name%20Like%20%22By%20Ctry*%22%20Then%0A%20%20%20%20sh.Name%20%3D%20sh.%5Be25%5D%20%26amp%3B%20%22_%22%20%26amp%3B%20sh.%5Bc28%5D%0A%20%20%20%20End%20If%0A%20%20%20%20%0ANext%20sh%0A%20%20%20%20%0AEnd%20Sub%0A%0AFunction%20getWorkSheet(ByVal%20WorkSheetName%20As%20String)%20As%20Worksheet%0A%20%20%20%20On%20Error%20GoTo%20EH%0A%20%20%20%20Set%20getWorkSheet%20%3D%20Worksheets(WorkSheetName)%0A%20%20%20%20Exit%20Function%0AEH%3A%0A%20%20%20%20Set%20getWorkSheet%20%3D%20Nothing%0AEnd%20Function%0A%0AFunction%20renameWorkSheet(ByRef%20ws%20As%20Worksheet%2C%20ByVal%20NewName%20As%20String)%20As%20Boolean%0A%20%20%20%20On%20Error%20GoTo%20EH%0A%20%20%20%20If%20getWorkSheet(NewName)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20ws.Name%20%3D%20NewName%0A%20%20%20%20%20%20%20%20renameWorkSheet%20%3D%20True%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20'New%20Worksheet%20Name%20already%20exists%0A%20%20%20%20%20%20%20%20renameWorkSheet%20%3D%20False%0A%20%20%20%20End%20If%0A%20%20%20%20Exit%20Function%0AEH%3A%0A%20%20%20%20renameWorkSheet%20%3D%20False%0AEnd%20Function%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-386545%22%20slang%3D%22en-US%22%3ERe%3A%20Needs%20Help%20With%20Conditional%20Renaming%20for%20Several%20Tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386545%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone!%20I%20have%20did%20most%20of%20the%20work%20myself%20but%20I%20need%20help%20with%20some%20editing.%20For%20example%2C%20I%20need%20to%20manually%20key%20in%20the%20command%20for%20ESD%20Trf%20Qty%20tab%20then%20it%20will%20know%20it%20must%20rename%20it%20to%26nbsp%3B%22ESD_%22%20%26amp%3B%20ws.Range(%22C28%22).Value%3CBR%20%2F%3E%3CBR%20%2F%3EI%20need%20help%20creating%20an%20array%20or%20editing%20it%20such%20that%20it%20knows%20that%20it%20has%20to%20rename%20all%20tabs%20which%20has%26nbsp%3B%20%22%20Trf%20Qty%22%20at%20the%20back%3CSPAN%3E%26nbsp%3Bthis%20way%20%22.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESub%20RenameWorkSheets()%3CBR%20%2F%3EDim%20ws%20As%20Worksheet%3CBR%20%2F%3E%3CBR%20%2F%3E'Rename%20Allocation%3CBR%20%2F%3ESet%20ws%20%3D%20getWorkSheet(%22Allocation%22)%3CBR%20%2F%3EIf%20Not%20ws%20Is%20Nothing%20Then%3CBR%20%2F%3ErenameWorkSheet%20ws%2C%20%22Master_%22%20%26amp%3B%20ws.Range(%22D28%22).Value%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3E'Rename%20ESD%20Trf%20Qty%3CBR%20%2F%3ESet%20ws%20%3D%20getWorkSheet(%22ESD%20Trf%20Qty%22)%3CBR%20%2F%3EIf%20Not%20ws%20Is%20Nothing%20Then%3CBR%20%2F%3ErenameWorkSheet%20ws%2C%20%22ESD_%22%20%26amp%3B%20ws.Range(%22C28%22).Value%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3E'Rename%3CBR%20%2F%3ESet%20ws%20%3D%20getWorkSheet(%22By%20Ctrn-EIN%22)%3CBR%20%2F%3EIf%20Not%20ws%20Is%20Nothing%20Then%3CBR%20%2F%3ErenameWorkSheet%20ws%2C%20ws.Range(%22E28%22).Value%20%26amp%3B%20ws.Range(%22C28%22).Value%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E'Your%20other%20worksheets%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3EFunction%20getWorkSheet(ByVal%20WorkSheetName%20As%20String)%20As%20Worksheet%3CBR%20%2F%3EOn%20Error%20GoTo%20EH%3CBR%20%2F%3ESet%20getWorkSheet%20%3D%20Worksheets(WorkSheetName)%3CBR%20%2F%3EExit%20Function%3CBR%20%2F%3EEH%3A%3CBR%20%2F%3ESet%20getWorkSheet%20%3D%20Nothing%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3CP%3EFunction%20renameWorkSheet(ByRef%20ws%20As%20Worksheet%2C%20ByVal%20NewName%20As%20String)%20As%20Boolean%3CBR%20%2F%3EOn%20Error%20GoTo%20EH%3CBR%20%2F%3EIf%20getWorkSheet(NewName)%20Is%20Nothing%20Then%3CBR%20%2F%3Ews.Name%20%3D%20NewName%3CBR%20%2F%3ErenameWorkSheet%20%3D%20True%3CBR%20%2F%3EElse%3CBR%20%2F%3E'New%20Worksheet%20Name%20already%20exists%3CBR%20%2F%3ErenameWorkSheet%20%3D%20False%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EExit%20Function%3CBR%20%2F%3EEH%3A%3CBR%20%2F%3ErenameWorkSheet%20%3D%20False%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
ONG ZHEN YANG RP
Contributor
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. 


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 :)
 
2 Replies

Hi everyone! I have did most of the work myself but I need help with some editing. For example, I need to manually key in the command for ESD Trf Qty tab then it will know it must rename it to "ESD_" & ws.Range("C28").Value

I need help creating an array or editing it such that it knows that it has to rename all tabs which has  " Trf Qty" at the back this way ". 


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

'Rename
Set ws = getWorkSheet("By Ctrn-EIN")
If Not ws Is Nothing Then
renameWorkSheet ws, ws.Range("E28").Value & 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

 

Solution

Hi everyone, I have figured it out myself.

 

The full code:

Sub RenameWorkSheets()
    Dim ws As Worksheet
    Dim sh As Worksheet
    
    'Rename Allocation
    Set ws = getWorkSheet("Allocation")
    If Not ws Is Nothing Then
         renameWorkSheet ws, "Master_" & ws.Range("D28").Value
    End If
    
    'Other worksheets
    For Each sh In Worksheets
    If sh.Name Like "*Trf Qty" Then
    sh.Name = Split(sh.Name, " ")(0) & "_" & sh.[c28]
    ElseIf sh.Name Like "By Ctry*" Then
    sh.Name = sh.[e25] & "_" & sh.[c28]
    End If
    
Next sh
    
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
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies