+ Reply to Thread
Results 1 to 5 of 5

Truncate Worksheet Name

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Truncate Worksheet Name

    I am using the code below to split one WorkSheet in to several tabs within the same WorkBook, based on the content of Column B. The macro automatically names each new tab, based on the text in Column B. The problem I have is that if the length of the text in Column B exceeds the 31 character limit, the sheet just gets named 'Sheet X', where 'X' is the next new sheet number, but in doing so, the data doesn't get copied to the sheet correctly.

    Is there a way to 'truncate' and sheet names the code generates, so that the sheet uses the Excel maximum of 31 characters, when running the code. I don't know enough about vba to understand which lines of code are generating the names in the first place, I assume it is the section around 'Get a temporary list of unique values from vCol'. I can't truncate the text on the master sheet because this is used in various lookups.

    Many thanks.

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Truncate Worksheet Name

    Hi,

    in this line the name is set:
    Please Login or Register  to view this content.
    to have a maximum of 31 chars:
    Please Login or Register  to view this content.
    NOTE: each time you refer to the sheet by name you have to use the "Left" function, alternatively you could use a worksheet object.
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  3. #3
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Truncate Worksheet Name

    Hi techneXus,

    Okay, I tried that and yes, that line of code does truncate the sheet names perfectly, however in doing so, it still causes a problem with the data being copied across. Basically any sheet generated where the text in Column B name originally exceeds the 31 character limit, doesn't get data copied over, those that are under 31 characters are fine. So I assume there must be some other code elements that need to be adjusted, I just don't know which ones and how to do that.

    I've attached a sample. If you run the macro 'Split31' and enter 3 in the dialgogue window, everything works fine, because all the text in Column C is below 31 characters in length and you will see that the sheet names are all good and the data is copied across correctly on each sheet. If you delete those extra sheets and rerun the macro, but enter 2 this time, so it uses the text in Column B, the sheets names are truncated, but for the sheets where the original name exceeds 31 characters, no data is copied across.

    Is there something else that needs to be changed?
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Truncate Worksheet Name

    Hi, I made some small adjustments: Split Worksheet 2.xlsm

  5. #5
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Truncate Worksheet Name

    Hi techneXus,

    Brilliant, that seems to have fixed the problem, was it easy to sort? My VBA knowledge is very limited, so it is difficult for me to tell.

    Many thanks for your help. I'll test this throuoghly now and let you know if I come across any more issues...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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