rearrange columns in all the sheets based on column header

newdb · #1 (**permalink**) 02-18-10, 00:53

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

Ax238 · #2 (**permalink**) 02-18-10, 11:48

In Excel:

Go to Data|Sort...
Click Options...
Under Orientation, select "Sort left to right"
Click OK
Click OK

Regards,

Ax

newdb · #3 (**permalink**) 02-18-10, 13:53

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

Ax238 · #4 (**permalink**) 02-19-10, 08:43

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

newdb · #5 (**permalink**) 02-20-10, 06:58

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...

newdb · #6 (**permalink**) 02-22-10, 01:45

Solution is here: Excel - Macro to rearrange/add columns in all the sheets based on column header - microsoft.public.excel.programming | Google Groups

Ax238 · #7 (**permalink**) 02-22-10, 09:06

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

Internet Services

Web Development

Digital Marketing

Consumer Tech