Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Excel Sort Macro (Excel 2000 >)

    I was recently asked if Excel can sort its sheets in alphabetical order. After some thought, I replied NO, but mentioned that a macro should be able to do the job. Below is a macro I created to sort sheets in ASC. or DESC. order.

    I have a 2 questions I would like to ask about the macro:
    1. Could the VBA experts review the code and advise me on any improvements or suggestions to tighten the edges!
    2. To assist me in adding the ability to sort sheets that have dates! (ie. Sheet1 name = Jan 2004, Sheet2 name = Feb 2004.)
    I attempted this and notice that when Jan 2004 is copied into a cell it pastes as 01/01/2004 (dd/mm/yyyy)! This causes a debug in VBA saying that subscript is out of range. I even tried storing the cell value in a variable declared as Date, but it still debugs to subscript out of range!
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Sort Macro (Excel 2000 >)

    See the thread starting at <post#=356749>post 356749</post#>.

  3. #3
    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: Excel Sort Macro (Excel 2000 >)

    The problem is that the "dates" are actually strings in the tabs and get converted to real dates in the spreadsheet. You could force it be text (but then you wouldn't sort by date but by alphabetically.

    You could add a column in your data to "store" the sheetname (as text), sort by date/text/number, etc, but rearrange by the other column,

    In GetSheetNames add the new line and modify another

    <pre> For i = 2 To Sheets.Count
    ActiveCell.Value = Sheets(i).Name
    ActiveCell.Offset(0, 1).Value = "'" & Sheets(i).Name 'NEW
    ActiveCell.Offset(0, 3).Value = i 'Modified
    ActiveCell.Offset(1, 0).Select
    Next i</pre>


    And in ArrangeSheets, modify these lines

    <pre> ShName = ActiveCell.Offset(0, 1) 'Modify
    ShPos = ActiveCell.Offset(0, 3).Value - 1 'Modify</pre>


    For those interested in some other variations to doing this, Chip Pearson has some code for Sorting Worksheets In A Workbook including only sorting some sheets, custom orders and doing a sort by sheet tab color(for xl2002 and higher) and then by name.

    Steve

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Sort Macro (Excel 2000 >)

    Thanx Steve. I'll make the changes and see how it goes. Chip Pearson's link seems to be a belter!!! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Thanx Hans.
    Regards,
    Rudi

Posting Permissions

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