Rename sheets based on the initial number of Characters

Miguelluis

New Member
Joined
Jan 29, 2013
Messages
42
Hi,

I've been searching for a Macro that enables me to rename various sheets in my workbook based on the initial few Characters and rename them to specific names. for example the sheets have the following names:
BBBO000102_abcd017_CancelledTrades2018-04-12-05-18-54
BBBO000102_abcd18_RedemptionAfterSale_Y2018-04-12-03-52-17
BBBO000102_abcf022_ClientCategory_CashInt_2018-04-12-07-12-22
ACBB000102BankAcHiRskCtryD201804120226
ACBB0001023rdPartyPayerD201804120226

The names in red are always the same, I wondered if anyone knows how to create a macro that would rename the above, every time (considering the black font changes daily) to the following:

CancelledTrades
RedemptionAfterSale
ClientCategoryCashInt
BankAcHiRskCtry
3rdPartyPayer

Thanks in advance
Miguel
 

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Given the 31 character limit on sheet names, how do your sheets have such long names?

WBD
 

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Sounds like sorcery at work. But this should do what you ask:

Code:
Public Sub RenameSheets()

Dim sheetMap As Variant
Dim thisSheet As Worksheet
Dim i As Long

sheetMap = Array("BBBO000102_abcd017_CancelledTrades", "CancelledTrades", "BBBO000102_abcd18_RedemptionAfterSale", "RedemptionAfterSale", "BBBO000102_abcf022_ClientCategory_CashInt", "ClientCategoryCashInt", "ACBB000102BankAcHiRskCtryD", "BankAcHiRskCtry", "ACBB0001023rdPartyPayerD", "3rdPartyPayer")
For Each thisSheet In ActiveWorkbook.Sheets
    For i = 0 To 8 Step 2
        If Left$(thisSheet.Name, Len(sheetMap(i))) = sheetMap(i) Then
            thisSheet.Name = sheetMap(i + 1)
        End If
    Next i
Next thisSheet

End Sub
WBD
 

Miguelluis

New Member
Joined
Jan 29, 2013
Messages
42
Thank you WDB, would this work if not all 5 sheets are there? Some days I will receive less reports.

Thanks

Sounds like sorcery at work. But this should do what you ask:

Code:
Public Sub RenameSheets()

Dim sheetMap As Variant
Dim thisSheet As Worksheet
Dim i As Long

sheetMap = Array("BBBO000102_abcd017_CancelledTrades", "CancelledTrades", "BBBO000102_abcd18_RedemptionAfterSale", "RedemptionAfterSale", "BBBO000102_abcf022_ClientCategory_CashInt", "ClientCategoryCashInt", "ACBB000102BankAcHiRskCtryD", "BankAcHiRskCtry", "ACBB0001023rdPartyPayerD", "3rdPartyPayer")
For Each thisSheet In ActiveWorkbook.Sheets
    For i = 0 To 8 Step 2
        If Left$(thisSheet.Name, Len(sheetMap(i))) = sheetMap(i) Then
            thisSheet.Name = sheetMap(i + 1)
        End If
    Next i
Next thisSheet

End Sub
WBD
 

Miguelluis

New Member
Joined
Jan 29, 2013
Messages
42
I get a run-time error 13, Type mismatch. Could this be caused by the residual date characters that are left (the black characters)?
 

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Oh. Do you have charts or something else in there as well? Try changing it to this:

Code:
For Each thisSheet In ActiveWorkbook.Worksheets
WBD
 

Forum statistics

Threads
1,082,478
Messages
5,365,786
Members
400,850
Latest member
Raj_Jpr

Some videos you may like

This Week's Hot Topics

Top