# Sort columns by column names

#### JanTo

##### New Member
Hi all,

Is it possible to sort colums by column name. E.g I have 4 columns named A,B,C and D. The initial data is not always in this order, it means that at first VBA code should find the column named B and put it first then find D column and put it second etc.
 A B C D 22 6 8 13 7 5 22 17 24 8 13 25 12 12 23 6

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

I would like to get result copied to the other sheet but with different order of columns:

 B D C A 22 4 6 18 5 2 17 1 15 3 8 7 9 22 24 14

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
------

sorry wrong approach

-------------

Last edited:
Hi,

in F1

=INDEX(\$A\$1:\$D\$5,ROW(\$A1),MATCH(LOOKUP(COLUMN(A\$1),ROW(\$A\$1:\$A\$4),{"B","D","C","A"}),\$A\$1:\$D\$1,0))

to be copied across

ABCDEFGHI
1ABCDBDCA
2226813613822
3752217517227
424813258251324
512122361262312

<tbody>
</tbody>
Foglio5

Worksheet Formulas
CellFormula
F1=INDEX(\$A\$1:\$D\$5,ROW(\$A1),MATCH(LOOKUP(COLUMN(A\$1),ROW(\$A\$1:\$A\$4),{"B","D","C","A"}),\$A\$1:\$D\$1,0))

<tbody>
</tbody>

<tbody>
</tbody>

Hope it helps

Hi,
It works well but I prefer vba, because i have a very large database and it is needed to be done once. Then new sorted database will be manipulated with other vba codes.

Thank you anyways,
Jan

Hi,

Can somebody tell my why recorded macro gives me an error when I´m trying to run it (marked with red):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

' copy Macro
'

'
Range("L30").Select
Cells.Find(What:="chassi", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("C26").Select
Cells.Find(What:="item name", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("B1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D26").Select
Cells.Find(What:="order date", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("C1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("I26").Select
Cells.Find(What:="internal del.date", After:=ActiveCell, LookIn:=xlValues _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("D1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("J26").Select
Cells.Find(What:="fetch date", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("E1").Select
ActiveSheet.Paste
Range("A46").Select
Sheets("Sheet1").Select
Range("L26").Select
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("F1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("T26").Select
Cells.Find(What:="text", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("G1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("AC26").Select
Cells.Find(What:="materialOk", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("H1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("BB26").Select
Cells.Find(What:="ppo", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("I1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("BO26").Select
Cells.Find(What:="wash", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("J1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("BP26").Select
Cells.Find(What:="pdi", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("K1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("BQ26").Select
Cells.Find(What:="last move", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Sheet2").Select
Range("L1").Select
ActiveSheet.Paste

This seems to do what you want for 500 rows.

Howard

Code:
``````Option Explicit

Dim lRowCount&
lRowCount = Cells(Rows.Count, "A").End(xlUp).Row

With Range("F1").Resize(lRowCount, 4)
.Formula = "=INDEX(\$[COLOR="#FF0000"]A\$1:\$D\$500[/COLOR],ROW(\$A1),MATCH(LOOKUP(COLUMN(A\$1),ROW([COLOR="#FF0000"]\$A\$1:\$A\$500[/COLOR]),{""B"",""D"",""C"",""A""}),\$A\$1:\$D\$1,0))": .Value = .Value
End With

Range("F1").Resize(lRowCount, 4).Cut Range("A1")

End Sub``````

Replies
5
Views
539
Replies
4
Views
473
Replies
0
Views
593
Replies
2
Views
160
Replies
0
Views
332

1,196,359
Messages
6,014,804
Members
441,847
Latest member
hw407

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back