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

    Sorting Worksheets in A>Z order (Excel 2000>)

    Hi all,

    Having a little more time to spare I keep myself in touch with VBA by practicing creating small macros for practice and for fun. I have been playing around with sorting sheets and have come up with a piece of code to do this. Since it is pet projects of mine and for learning more about VBA coding, I would appreciate any tips or advice from anyone on how to improve and streamline the code. I love to learn more in VBA, so please share any thoughts for my and other browsers benefit. Cheers.

    Sorting worksheets in alphabetical order
    Option Explicit

    Sub SortSheets()
    Dim Response As String
    Dim i As Integer
    Dim myS As Worksheet

    'Confirm action
    Response = MsgBox("This macro will re-arrange your worksheets! " _
    & "Do you want to continue?", vbYesNo + vbExclamation)
    If Response = vbNo Then
    Exit Sub
    End If

    'Confirm Sheet Delete
    Response = MsgBox("If there is a sheet named 'Sheet List' in your worksheets, " _
    & "it sheet will be deleted! Continue?", vbYesNo + vbExclamation)
    If Response = vbNo Then
    Exit Sub
    End If

    Application.ScreenUpdating = False
    For Each myS In Worksheets
    If myS.Name = "Sheet List" Then
    Application.DisplayAlerts = False
    myS.Delete
    End If
    Next myS
    Application.DisplayAlerts = True


    'STEP 1 : Set up Sheet List
    Sheets.Add before:=Sheets(1)
    ActiveSheet.Name = "Sheet List"
    Range("A1").Value = "Sheet List"

    'STEP 2 : Create Sheet List
    For i = 2 To Sheets.Count
    Range("A" & i).Value = Sheets(i).Name
    ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & i), Address:="", SubAddress:= _
    Sheets(i).Name & "!A1", TextToDisplay:=Sheets(i).Name
    Next i
    Range("A1").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes _
    , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

    'STEP 3 : Sort Sheets
    For i = 2 To Sheets.Count - 1
    On Error Resume Next
    Sheets("Sheet List").Select
    Sheets(Range("A" & i).Value).Move before:=Sheets(i)
    Next i
    On Error GoTo 0
    Sheets("Sheet List").Select
    Range("A1").Font.Bold = True
    Application.ScreenUpdating = True

    'STEP 4 : Final Steps
    Response = MsgBox("Do you want to keep the Sheet List?" _
    , vbYesNo + vbInformation)
    If Response = vbNo Then
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    End If
    End Sub
    Regards,
    Rudi

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

    Re: Sorting Worksheets in A>Z order (Excel 2000>)

    You don't need to place the sheet names in a sheet - see for example Sorting And Ordering Worksheets on Chip Pearson's site.

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

    Re: Sorting Worksheets in A>Z order (Excel 2000>)

    Many thanks for the link. I was aware that Chip Pearson had macros to sort sheets, and I am also familiar to the ASAP option to do the same, but Chip is miles ahead of my tamperings with VBA. When I read through his code I only understand 1/3rd of it. It is a great place to learn and browse through high end code, but for now I prefer to create my own code to get the feel of it and learn. I think lots of loungers will also understand the innards of my code better in their attempts to learn code too. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rudi

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

    Re: Sorting Worksheets in A>Z order (Excel 2000>)

    If you want code that is (relatively) easy to understand, try this. It uses the bubble sort method, which is fast enough for this purpose.

    Sub SortWorksheets()
    Dim n As Integer
    Dim i As Integer
    Dim j As Integer
    Application.ScreenUpdating = False
    n = Worksheets.Count
    For i = 1 To n - 1
    For j = i + 1 To n
    If Worksheets(j).Name < Worksheets(i).Name Then
    Worksheets(j).Move Before:=Worksheets(i)
    End If
    Next j
    Next i
    Application.ScreenUpdating = True
    End Sub

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

    Re: Sorting Worksheets in A>Z order (Excel 2000>)

    I just cannot believe how flexible code is. Fancy that! Hans, this just so proves that one should NEVER dive straight in and create code that comes to mind first. If one would just think of what you want to do and plan what is the BEST method to do it in, even asking if there is not something in Excel that can already do the job, and then code around that, the macro becomes shorter, easier and faster...
    TX.
    I cannot tell you how many times I have recieved answers that just blow me away!
    Cheers
    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
  •