Shorting a long title to a worksheet tab

  • Thread starter Thread starter Brian S
  • Start date Start date
B

Brian S

IS there an easy way to shorten a project name like "Advanced Network
Application Production" to something that will easily fit on a tab. Right
now I am just taking the first 31 characters, but some projects have the same
first 31 characters. I Found away around that, but it is not very
descriptive. I was thinking something like searching for the spaces between
words and then taking the first 3 or 4 letters of each word. Is that
possible?
 
Try using an acronym like "ANAP" or you could do what you wanted to do "Adv
Net App Pro". The code below will take the first 3 letters of each word and
rename the worksheet. It will scan each sheet in the workbook. Hope this
helps! If so, let me know, click "YES" below.

Sub RenameSheets()

Dim Letters As Long
Dim wks As Worksheet
Dim aryName As Variant
Dim i As Long
Dim strNewName As String

' the amount of letters to get from sheet name
Letters = 3

' scan each worksheet in workbook
For Each wks In Worksheets

' form array of words in wks name
aryName = Split(wks.Name)

' get first 3 letters of
For i = LBound(aryName) To UBound(aryName)
strNewName = strNewName & Left(aryName(i), Letters) & " "
Next i

' change wks name to new name
wks.Name = Trim(strNewName)

' reset new name string
strNewName = ""
Next wks

End Sub
 
Back
Top