Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2015
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Thumbs up Sorting Worksheets Based On Specific Cells Values

    Regarding this Thread: http://windowssecrets.com/forums/sho...-MS-Excel-2010

    I see that this thread went with sorting the sheets themselves by name, but I am interested in how to sort the sheets based on a specific value on each sheet. Specifically, I would like to arrange the sheet based on a date that I enter in each sheet(a specific cell can be used for all the sheets). The date would change based on when I need to use that sheet again, and I want to organize all the sheets in ascending order by date.

    From what I have read, I believe, if possible, this will have be done via Macro, but before I figured out how to accomplish that, I wanted to see if it even possible.

    What do you think? It seems, based on your reply, that you may know how to accomplish what I am seeking.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    DWood,

    The following code will sort the dates in cells A2 on each of the sheets using an array variable. Then using the array variable, it will sort the sheets in ascending order according to the order of the date on that sheet.

    Code:
    Public Sub SortSheets()
    Application.ScreenUpdating = False
    '--------------------------------
    'DECALRE AND SET VARIABLES
    Dim s() As Variant
    Dim I As Integer, J As Integer
    num = Worksheets.Count
    ReDim s(num), t(num)
    '--------------------------------
    'SORT DATES
    For I = 1 To num:: s(I) = Worksheets(I).Cells(2, 1):: Next I
    For I = 1 To num:: t(I) = Worksheets(I).Name:: Next I
    For I = 1 To num - 1
        For J = I + 1 To num
            If s(I) > s(J) Then
                dte = s(I):: s(I) = s(J):: s(J) = dte
                sht = t(I):: t(I) = t(J):: t(J) = sht
            End If
        Next J
    Next I
    '--------------------------------
    'SORT SHEETS
    For I = 1 To num
        For J = 1 To num
            If Worksheets(t(J)).Cells(2, 1) = s(I) Then
                Worksheets(t(I)).Move before:=Worksheets(I)
            End If
        Next J
    Next I
    Application.ScreenUpdating = True
    End Sub
    In the attached sample, move the sheet tabs to different tab positions then run the code. For your convenience, I have placed a sort button on each sheet. The shhets will be re-ordered according to the dates in cells A2.

    HTH,
    Maud
    Attached Files Attached Files

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    DWood360 (2015-03-22)

  4. #3
    New Lounger
    Join Date
    Mar 2015
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Perfect. I will start looking up how to interpret the code so I can modify it for what I am trying to do. Thank you showing me it is possible, very awesome of you.

Posting Permissions

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