Results 1 to 12 of 12
  1. #1
    New Lounger allman71's Avatar
    Join Date
    Dec 2009
    Location
    Statesville, NC, USA
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Sort worksheets in MS Excel 2010

    Is it possible to sort all the worksheets in a work book and if so, how is it done? Also, can I format one sheet and have all the other sheets format the same?

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,243
    Thanks
    202
    Thanked 796 Times in 729 Posts
    I don't know how to do the sorting you want but as for the formatting if you select a range of sheets, e.g. click on the 1st hold Shift & click on the last or just hold Ctrl as you pick at random then any formatting applied to the 1st sheet will apply to all.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. The Following 2 Users Say Thank You to RetiredGeek For This Useful Post:

    abaas (2011-11-09),allman71 (2011-11-08)

  5. #3
    New Lounger allman71's Avatar
    Join Date
    Dec 2009
    Location
    Statesville, NC, USA
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you for the info on applying the format to all the sheets. That will help me a lot.

  6. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,094
    Thanks
    13
    Thanked 37 Times in 36 Posts
    Microsoft provides this macro to sort the sheet tabs:

    Sub Sort_Active_Book()
    Dim i As Integer
    Dim j As Integer
    Dim iAnswer As VbMsgBoxResult
    '
    ' Prompt the user as which direction they wish to
    ' sort the worksheets.
    '
    iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
    & "Clicking No will sort in Descending Order", _
    vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
    For i = 1 To Sheets.Count
    For j = 1 To Sheets.Count - 1
    '
    ' If the answer is Yes, then sort in ascending order.
    '
    If iAnswer = vbYes Then
    If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
    Sheets(j).Move After:=Sheets(j + 1)
    End If
    '
    ' If the answer is No, then sort in descending order.
    '
    ElseIf iAnswer = vbNo Then
    If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
    Sheets(j).Move After:=Sheets(j + 1)
    End If
    End If
    Next j
    Next i
    End Sub

  7. #5
    New Lounger allman71's Avatar
    Join Date
    Dec 2009
    Location
    Statesville, NC, USA
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    How do I use this macro? Can I copy and paste it within excel and if so, where do I paste it?

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,243
    Thanks
    202
    Thanked 796 Times in 729 Posts
    Do you wish to sort the Sheets themselves into order by name (as the macro above does) or are you wishing to sort the data on each sheet. I ask this question since I'm not sure and the question about formatting would lead me to think you wanted the latter, but then again that is just a guess.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,094
    Thanks
    13
    Thanked 37 Times in 36 Posts
    Yes, copy it. Then ALT+F11 will open the VBA screen. Right click your spreadsheet and insert a module. Paste this into the blank module. Close the VBA screen. Then, run the macro from the developer tab (in Office 2007 or 2010). The macro sorts the sheet tabs, not the data in any of the sheets.
    Last edited by kweaver; 2011-11-08 at 19:05. Reason: add'l info

  10. The Following User Says Thank You to kweaver For This Useful Post:

    allman71 (2011-11-10)

  11. #8
    New Lounger allman71's Avatar
    Join Date
    Dec 2009
    Location
    Statesville, NC, USA
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I want to sort the sheets. I have named each sheet and now want to put them in alphabetical order.

    The format question was to apply format to all sheets, but I want to put the individual sheets in alphabetical order.

  12. #9
    New Lounger allman71's Avatar
    Join Date
    Dec 2009
    Location
    Statesville, NC, USA
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I will try this tomorrow evening. It is time to call it a day now. Thanks for the help.

  13. #10
    New Lounger allman71's Avatar
    Join Date
    Dec 2009
    Location
    Statesville, NC, USA
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I ran the macro and now all my sheets are in alphabetical order just like I wanted.

    Thanks for the help.

  14. #11
    New Lounger
    Join Date
    Oct 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I ran the macro, and I get this error: Run-time error '1004': Method 'Sheets' of object'_Global' failed
    When I select debug, this line of the macro is highlighted: For i = 1 To Sheets.Count
    I saw this macro on another website, which said it applied to Excel 2007 and earlier. Is this failing because I am running it in Excel 2010?

  15. #12
    New Lounger allman71's Avatar
    Join Date
    Dec 2009
    Location
    Statesville, NC, USA
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I have used it several times in my MS Office Excel 2010 with no problems.

Posting Permissions

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