Unanswered: rearrange columns in all the sheets based on column header
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
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
How do I accomplish this ?
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.
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.
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.
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
OrderCustom:=6, MatchCase:=False, Orientation:=xlLeftToRight,
The only thing I need here is to run above code for all the sheets in the workbook but I haven't succeeded yet...
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, _