+ Reply to Thread
Results 1 to 26 of 26

VBA Create Sheet Names Automatically Shortening Sheet Name

  1. #1
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    VBA Create Sheet Names Automatically Shortening Sheet Name

    Hi, I wonder whether someone may be able to help me please.

    I'm using the code below to create worksheets from the value in a specific cell range.

    Please Login or Register  to view this content.
    The issue I have is that I know in Excel 2003 there is a sheet name length of 31 charcters, so when the code finds a cell value greater than this, it throws an error.

    This is the line which creates the sheet name .Name = UqPo(i, 1). Could someone perhaps tell me please is there a way whereby when the script reaches a cell with a value greater than 31 characters, when it creates the sheet, it automatically shortens the sheet name to 30 charcaters and then a full stop to reach the 31 character maximum.

    Many thanks and kind regards

    Chris

  2. #2
    Forum Contributor pareshj's Avatar
    Join Date
    05-20-2014
    Location
    MUMBAI
    MS-Off Ver
    2007 & 2010
    Posts
    447

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Hi hobbiton73,

    You can use Left function to allow string of only 30 characters.

    Please Login or Register  to view this content.
    Hope this will help you.


    Regards,
    Paresh J
    Click on "* Add Reputation" as a way to say thanks

  3. #3
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Hi @Paresh J, thank you for taking the time to come back to me with this.

    As you were kindly putting your solution together which works perfectly, I had also found the 'Left 30' formula, so it't good to know I was on the right track.

    Forgive me for asking another question, which admitedly, is a little off the original post topic, but could tell me please, would it be possible to limit the creation of the sheets by only creating sheets where the cell value does not contain "TM -".

    I had put this piece of code together, but I can't get it to work.

    Please Login or Register  to view this content.
    Many thanks and kind regards

    Chris

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    if the array already has the cell values you oughta refer to the array not the cell
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Hi @AB33, thank you for taking the time to reply to my post with the solution. As with the solution which @JosephP kindly provided the solution does work, but for some strange reason there are also blank sheets created.

    Many thanks and kind regards

    Chris

  7. #7
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Hi @JosephP, thank you very much for taking the time to reply to my post and for the solution.

    The solution works great, but the problem I have is that the for a strange reason there are blank sheets created as well.

    Many thanks and kind regards

    Chris

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Chris,
    When a code creates a sheet and the sheet being created is blank, it is highly likely that the sheet name is not valid name.
    The cell(array) which uses to create sheet has some invalid chr.

  9. #9
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Hi @AB33, thank you for coming back to me with this.

    I think I've found the issue it seems that the code falters when there are special characters in the cell value e.g. /,\() and numbers. Forgive me because my lack of knowledge lets me down here, but could you tell me please, is there a way to get around this.

    Many thanks and kind regards

    Chris

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Chris,
    I have made a small code which shows you how to overcome the invalid names. I have tested it on my sample using column C range. You need to change the cell in to your own array.

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Hi @AB33, that's absolutely great, thank you very much.

    I've tried the code, but unfortunately when I run this I recieve a 'Type Mismatch' error on this line:
    UqPo(i, 1) = Replace(Replace(Replace(UqPo(i, 1), "(", ""), "\", ""), ")", "")

    Using debug, I've been able to find out that the value it's failing on is "Platform Architecture".

    For ease, I've inserted my full script below.

    Please Login or Register  to view this content.
    I'm really sorry to be a complete pain with this!

    All the best and kind regards

    Chris
    Last edited by hobbiton73; 08-29-2014 at 05:44 AM.

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Chris,
    Try this one and see where it errors.

    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Hi @AB33, thank you very much for take the time to put this together and my apologies for not replying sooner.

    Unfortunately, I was unable to test this before I left for today, but I will let you know how I get on, on Monday.

    All the best and kindest regards.

    Chris

  14. #14
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Hi @AB33 I hope you are well.

    As promised I've tested the code this morning, and unfortunately I'm still receiving incorrect sheet data.

    I have however realised why the code is creating the blank sheets called "Sheet1", "Sheet 2" etc. Although the following line of code stops the data from extracted, the new sheets are still being created If InStr(1, UqPo(i, 1), "TM - ", vbTextCompare) + InStr(1, UqPo(i, 1), "Enhancements ", vbTextCompare) = 0 Then

    In addition when a sheet is created and the data is correctly extracted, when the code finds the same value in column F on the "All Data" sheet again, it tries to create a new sheet, rather than pasting to the pre-exisitng sheet.

    It is these two errors that I feel are the most important to fix, so I'm now starting to look at these.

    Many thanks and kind regards

    Chris

  15. #15
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Chris,
    It is difficult to see with out a sample data to test it on.
    The code has many other lines.

    Please Login or Register  to view this content.
    if the above two lines return false, the code still creates new sheets, but will not rename them with the value of UqPo(i, 1),
    Please Login or Register  to view this content.
    Last edited by AB33; 09-01-2014 at 05:38 AM.

  16. #16
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,119

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Perhaps try this one:
    Please Login or Register  to view this content.
    Last edited by romperstomper; 09-02-2014 at 02:46 AM. Reason: Amended code
    Everyone who confuses correlation and causation ends up dead.

  17. #17
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Hi @AB33, thank you very much for taking the time to come back to me with this.

    I have built a test file at home, so I will post this, so you will be able to look at the data. I would have done this yesterday evening but there appeared to be problem with accessing this site.

    Many thanks and kind regards

    Chris

  18. #18
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Hi @romperstomper, thank you very much for taking the time to reply to my post and for putting this together.

    I've tried the code you kindly provided, but unfortunately I'm receiving a 'Subscript out of range' error at this line: rngFilter.AutoFilter Field:=1, Criteria1:=UqPo(i, 1)

    I've checked the sheet names you've referenced and ranges selected in your code, but I'm unable to find an error here, so I'm a little unsure where the problem is.

    Many thanks and kind regards

    Chris

  19. #19
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,119

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Hi Chris,

    Sorry - I forgot to change a couple of lines when I switched to use a dictionary. I've amended the code in my last post - please test and let me know.

  20. #20
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Hi @romperstomper, there is absolutely no need to apologise. Your help is truly appreciated.

    I've re-run the code, and you've certainly solved the issue of excluding the cell values containing "Enhancements" or "TM - ". The problem I now have, and I apologise for, is for example, if I have a cell value of "Digital Investigation September Version 1" which is at the beginning of my data range the code correctly shortens this to "Digital Investigation September" and extracts the relevant data.

    Then, later on in my data range I have the cell value "Digital Investigation September Version 2", but shortening this means it would also create a sheet called "Digital Investigation September", so the code stops and displays an error message "Cannot rename a sheet to the same name as another sheet, a referenced object ibrary or a workbook referenced by Visual Basic'.

    Many thanks and kind regards

    Chris

  21. #21
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,119

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    What do you want to happen in that situation?

  22. #22
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Hi @romperstomper, I really appreciate you coming back to me with this so quickly.

    Having thought about this I think that the best option would be to reduce the character length to 21 on this line GetValidSheetName = Left$(strOrig, 31), and then where two sheets have the same name add the word "Project V" and then number of the sheet. For example if it's the second iteration it will be "Project V2", the third iteration "Project V3".

    I hope this helps, and I'm very sorry to put you to all this trouble.

    Many thanks and kind regards

    Chris

  23. #23
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,119

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Untested:
    Please Login or Register  to view this content.
    Note that this will not rename the first sheet where there are multiple matches, and it will error if you have more than 9 sheets matching the same first 21 characters.

  24. #24
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Hi @romperstomper, thank you very much for coming back to me with this. It works perfectly.

    I really can't thank you enough because I've been working on this solidly for the last week inc. weekends, so it's a great relief.

    May I just ask please, could you possibly add some notes to the 'Function' pieces of code, so that I can learn from this. May I aslo ask, could you perhaps also explain what the 'Function' statement actually does, if it's not too much trouble.

    Many thanks and kind regards

    Chris

  25. #25
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,119

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    There are two main types of routine - Function and Sub. The main difference between the two is that Function usually returns a value.

    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.

  26. #26
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Create Sheet Names Automatically Shortening Sheet Name

    Hi @romperstomper, that's fantastic, thank you so much.

    Have a good day, all the best and kind regards

    Chris

+ 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] Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACRO
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-29-2013, 03:24 PM
  2. [SOLVED] Simple macro to create list of sheet names and the contents of cells A1 & N1 of each sheet
    By atcsmh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-15-2013, 11:11 AM
  3. Macro in sheet one to create a search for names in sheet 2
    By CamillaKb in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-16-2012, 08:17 AM
  4. Create array of file names/sheet names
    By BVHis in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-06-2008, 11:30 AM
  5. Replies: 0
    Last Post: 11-13-2007, 06:58 AM

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