+ Reply to Thread
Results 1 to 4 of 4

truncate worksheet name if >31 characters to worksheet

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-17-2015
    Location
    Roanoke, Virginia
    MS-Off Ver
    2010
    Posts
    173

    truncate worksheet name if >31 characters to worksheet

    This code works great for creating worksheets from a list. However some names on the list are longer than the 31 characters allowed. Is there a way to edit this code to truncate the name if larger than 31 characters?

    Sub AddWorksheetsFromSelection()
        Dim CurSheet As Worksheet
        Dim Source As Range
        Dim c As Range
    
        Set CurSheet = ActiveSheet
        Set Source = Selection.Cells
        Application.ScreenUpdating = False
    
        For Each c In Source
            sName = Trim(c.Text)
            If Len(sName) > 0 Then
                    Worksheets.Add After:=Worksheets(Worksheets.Count)
                    ActiveSheet.Name = sName
            End If
        Next c
    
        CurSheet.Activate
        Application.ScreenUpdating = True
    End Sub

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: truncate worksheet name if >31 characters to worksheet

    Maybe this.
    Sub AddWorksheetsFromSelection()
        Dim CurSheet As Worksheet
        Dim Source As Range
        Dim c As Range
    
        Set CurSheet = ActiveSheet
        Set Source = Selection.Cells
        Application.ScreenUpdating = False
    
        For Each c In Source
            sName = Trim(c.Text)
            If Len(sName) > 0 Then
                    Worksheets.Add After:=Worksheets(Worksheets.Count)
                    ActiveSheet.Name = Left(sName, 31)
            End If
        Next c
    
        CurSheet.Activate
        Application.ScreenUpdating = True
    End Sub
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  3. #3
    Forum Contributor
    Join Date
    07-17-2015
    Location
    Roanoke, Virginia
    MS-Off Ver
    2010
    Posts
    173

    Re: truncate worksheet name if >31 characters to worksheet

    Thanks - that works
    Appreciate your time and effort.

  4. #4
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: truncate worksheet name if >31 characters to worksheet

    My pleasure, thanks for the feedback and rep. points.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Replace accented characters in worksheet name to regular characters
    By kingsdime29x in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-18-2017, 10:59 AM
  2. [SOLVED] truncate a string x characters after a delimiter
    By timmatthews in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-19-2013, 05:56 PM
  3. Truncate Worksheet Name
    By HangMan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-31-2013, 05:55 AM
  4. Replies: 3
    Last Post: 09-09-2010, 10:41 AM
  5. Truncate all characters left of 6th character
    By fredmeister in forum Excel General
    Replies: 3
    Last Post: 02-09-2010, 04:41 PM
  6. Replies: 4
    Last Post: 01-13-2006, 09:55 AM
  7. Excel Worksheet Subtotals Data Truncate
    By tomwashere2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-15-2005, 10:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1