Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    worksheet names as numbers (2003)

    I have a workbook with 150 worksheets where the worksheet names relate to project numbers such as 105214 and 9419455

    I have written code that places the worksheet names in an array and sorts the array into numeric order. However the following line of code returns an error because VBA is trying to access sheet number 105214.

    ThisWorkbook.Worksheets(SortedArrayWorksheetNames( i)).Move Before:=ThisWorkbook.Worksheets(SortedArrayWorkshe etNames(i + 1))

    Can someone please advise how I can make VBA look for a worksheet with name 105214 and put it before the worksheet with name 105227

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: worksheet names as numbers (2003)

    It should work if you create the array as a string array rather than a variant. if it is a variant array the names could be interpreted as numbers. if it is explictly defined as a string you should not have that problem.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: worksheet names as numbers (2003)

    Steve

    Given that I have got so far, is there a quick conversion function so that I can convert all the entries in SortedArrayWorksheetNames and create a text version of this array?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: worksheet names as numbers (2003)

    something like:

    for i = lbound(SortedArrayWorksheetNames) to ubound(SortedArrayWorksheetNames)
    SortedArrayWorksheetNames(i) = cstr(SortedArrayWorksheetNames(i))
    next


    Of course you could define the array first:

    dim SortedArrayWorksheetNames () as string

    and then they will go in as strings.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: worksheet names as numbers (2003)

    Thanks

    That has cracked the problem

Posting Permissions

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