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, the...

example.xlsm31 KB

Macros and VBA

ONG ZHEN YANG RP

Mar 28, 2019

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

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

Forum Discussion

Needs Help With Conditional Renaming for Several Tabs

Resources