Rename sheets based on the initial number of Characters

[Miguelluis]

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]

Given the 31 character limit on sheet names, how do your sheets have such long names?

WBD

 

[wideboydixon]

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

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]

it cuts the dates, but the characters in red always fit.

 

[Miguelluis]

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:

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

 

[wideboydixon]

Yep. It just renames the ones it finds.

WBD

 

[Miguelluis]

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]

Uhoh. Which line?

 

[Miguelluis]

For Each thisSheet In ActiveWorkbook.Sheets

 

[wideboydixon]

Oh. Do you have charts or something else in there as well? Try changing it to this:

For Each thisSheet In ActiveWorkbook.Worksheets

WBD