Sorting ws by headers in 1st row, skipping 2nd

[elisep]

Hello!!

Yet another question for this stellar community...

I'd like to be able to sort my worksheets by the data headers in the first row (or if not those, then I suppose by column numbers, although words would be easier). However, there's a single merged cell in the second row(spanning the entire page, as a visual separator) which prevents any easy-access-Excel-preset sorting. And I haven't found a way to specify that my table has two header rows (is this possible? the second row doesn't really have any content, after all...).

So, it looks like I'll need to figure out some macro action. But that's above my head, I'm afraid. And so I turn once again to you wonderful people who help the code-challenged like myself.

Any suggestions? Please???
(sorry for the dismal tone -- it's after 5pm on a Friday, and MY choice would be to remove that culprit second line, and put the information in the document header...)

Thank you in advance, fine people.

elise

 

[Tazguy37]

Where is the header you want to sort by, exactly?

 

[elisep]

Well my columns are A through J. Currently we're sorting by A (project name), D (total cost), E, and J (negotiator). Conceivably, I think they might realize at some future date that they'd like to sort by column B and by column C (date received).

The data is fairly unique in most of the cells. As in, there often aren't secondary sorts. Except, for example, if we're sorting by D (cost), then the project without assigned costs will need a secondary sort by column A (name). And there are only a handful of negotiators, so a secondary sort would be good there too -- like by title, or by cost, or by date.

Is it just me, or would this be significantly easier with those Excel data sort features?

Thank you for replying! It's giving me hope...

elise

 

[Tazguy37]

If you want to sort by a particular cell in each sheet, try this code in a standard module:

Sub SortWorksheets()
'Posted by Chip Pearson 07/31/2003
'http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/2ac0e519ca8438dd/19ef3037af415484%2319ef3037af415484?sa=X&oi=groupsr&start=0&num=3

Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

    SortDescending = False

    If ActiveWindow.SelectedSheets.Count = 1 Then
        FirstWSToSort = 1
        LastWSToSort = Worksheets.Count
    Else
        With ActiveWindow.SelectedSheets
            For N = 2 To .Count
                If .Item(N - 1).Index <> .Item(N).Index - 1 Then
                    MsgBox "You cannot sort non-adjacent sheets"
                    Exit Sub
                End If
            Next N
            FirstWSToSort = .Item(1).Index
            LastWSToSort = .Item(.Count).Index
         End With
    End If


    For M = FirstWSToSort To LastWSToSort
        For N = M To LastWSToSort
            If SortDescending = True Then
                If Worksheets(N).Range("A1") > Worksheets(M).Range("A1") Then
                    Worksheets(N).Move Before:=Worksheets(M)
                End If
            Else
                If Worksheets(N).Range("A1") < Worksheets(M).Range("A1") Then
                    Worksheets(N).Move Before:=Worksheets(M)
                End If
            End If
        Next N
    Next M

End Sub

• Press Alt-F11 to open the VBE.
  Press Control-R to open the Project Explorer. (May be open already)
  Click "Microsoft Excel Objects" for the file you're working on (should expand the list of the ThisWorkbook module and any sheet modules.)
  
  Select Insert, Module from the drop down menus.
  
  Put your code in the right-hand window.
  Press Alt-Q to close the VBE and return to Excel.

Hope that helps!