Renaming multiple tabs from cell - how to limit to 30 characters?

[Gerald Crumbley]

I have a monthly report to produce, I have 51 .xlsx sheets to combine. I have found how to import all into different worksheets. I have found how to rename each worksheet to a cell value.

Problem: the tabs can only be 31 characters or less, or the script doesn't rename it.
Question: How can I cut the cell characters to 30 before running the rename script?

Below is my Tab Rename script. If I can add to this to cut the cells to 30, great. If I need to create a new macro, please let me know. Thanks for any advice or direction:

Sub RenameTabs()
Dim ws As Worksheet
For Each ws In Worksheets
On Error Resume Next
If Len(ws.Range("F5")) > 0 Then
ws.Name = ws.Range("F5").Value
End If
On Error GoTo 0
Next
End Sub

 

[Joe4]

Welcome to the Board!

Maybe change this:

ws.Name = ws.Range("F5").Value

to this:

ws.Name = LEFT(ws.Range("F5").Value,30)

 

[Gerald Crumbley]

Welcome to the Board!

Maybe change this:

ws.Name = ws.Range("F5").Value

to this:

ws.Name = LEFT(ws.Range("F5").Value,30)

OMG! I have worked hours on this! I tried that but I was putting it on a separate line. You are wonderful! Thank you!

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[rainbow7766]

Hi Joe4,
how do skip first and second tab? start rename only on third tab?

 

[Joe4]

Hi Joe4,
how do skip first and second tab? start rename only on third tab?

You can do that like this:

Sub RenameTabs()

    Dim i As Long
    Dim ws As Worksheet

    For i = 3 To Sheets.Count
        Set ws = Sheets(i)
        On Error Resume Next
        If Len(ws.Range("F5")) > 0 Then
            ws.Name = Left(ws.Range("F5").Value, 30)
        End If
        On Error GoTo 0
    Next i

End Sub

 

[rainbow7766]

You can do that like this:

Sub RenameTabs()

    Dim i As Long
    Dim ws As Worksheet

    For i = 3 To Sheets.Count
        Set ws = Sheets(i)
        On Error Resume Next
        If Len(ws.Range("F5")) > 0 Then
            ws.Name = Left(ws.Range("F5").Value, 30)
        End If
        On Error GoTo 0
    Next i

End Sub

Hi Joe4,

Thank you very much!

 

[Joe4]

You are welcome.