Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2009
    Posts
    13

    Unanswered: rearrange columns in all the sheets based on column header

    Hi,
    I need to rearrange columns across all the sheets in a certain order based on
    column headers (those are namely these: "NUM", "SYS", "DIA", "TAG" etc.... )
    since I have them now randomly placed across the sheets in different
    columns.
    For example, my columns might come into spreadsheet as
    SYS, TAG, NUM, DIA or as TAG, NUM, DIA, SYS etc.
    I want them all sorted the same way - the rest of the columns is
    irrelevant.
    How do I accomplish this ?
    TIA

  2. #2
    Join Date
    May 2009
    Posts
    258
    In Excel:
    1. Go to Data|Sort...
    2. Click Options...
    3. Under Orientation, select "Sort left to right"
    4. Click OK
    5. Click OK


    Regards,

    Ax

  3. #3
    Join Date
    Jun 2009
    Posts
    13
    Thx Ax that would work provided custom list order is in place which is not a big deal.
    But I have 100+ sheets and I want to sort couple of them (based on a custom list which exists) in all the sheets.
    I guess I need macro/VBA.
    Any help please.
    TIA
    Last edited by newdb; 02-18-10 at 15:09.

  4. #4
    Join Date
    May 2009
    Posts
    258
    Excel makes it easy to create macros. Just start the macro recording before you perform the actions and stop the macro once you are finished. To start recording a macro, just go to Tools|Macro|Record New Macro....

    You may have to make changes to it, like generalizing and putting it in a loop over the sheets, but this should get you going in the right direction. To make the macro cleaner, try using keyboard shortcuts. For example, you can select all used cells by clicking on cell A1, then pressing Ctrl-Shift-End. After this you can perform the sort as above.

    If I'm still missing the mark, please explain in more detail and/or post an example spreadsheet.

    Ax

  5. #5
    Join Date
    Jun 2009
    Posts
    13
    Based on your guidance Ax (thanx for it!) I have created Custom List that defines desired columns order. (see below OrderCustom:=6) and it works on the selected sheet.
    ----------------------
    Sub Macro1()
    Cells.Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
    Header:=xlGuess, _
    OrderCustom:=6, MatchCase:=False, Orientation:=xlLeftToRight,
    _
    DataOption1:=xlSortNormal
    End Sub
    ----------------------
    The only thing I need here is to run above code for all the sheets in the workbook but I haven't succeeded yet...

  6. #6
    Join Date
    Jun 2009
    Posts
    13

  7. #7
    Join Date
    May 2009
    Posts
    258
    You can easily run that over all sheets:
    Code:
    Sub SortColumns()
        Set wbActive = Application.ActiveWorkbook
        For i = 1 To wbActive.Sheets.Count
            Set wsCurrent = wbActive.Sheets(i)
            wsCurrent.Cells.Sort Key1:=wsCurrent.Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=6, MatchCase:=False, Orientation:=xlLeftToRight, _
            DataOption1:=xlSortNormal
        Next
    End Sub
    I tested, it should work.

    Ax
    Last edited by Ax238; 02-22-10 at 10:09.

Posting Permissions

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