How to rearrange the excel columns by the columns header name

Question

How could I rearrange the columns by the columns header name. I need to process a report on weekly basis,the raw worksheet has 23 columns.I abstract a part of them as example.

Raw columns sequence： QTY Payment Terms Contract No. etc
Desired columns sequence:Contract No. Payment terms QTY etc

Any idea how to automatize the columns rearrangement with VBA code? Many thanks in advance.

Are you looking to arrange the column headers to be in alphabetical order? If so, use the sort function. — davidmneedham, Oct 02 '17 at 16:25
Your best bet is using tables. Insert a table into your data range and then the first row will be your headers. Through built-in functions for tables you can select the whole column using only the header e.g. `Activesheet.listobjects("table1").listcolumns("mycolumn").range` if you share a code and show what you are trying to do we can help more specificly — Ibo, Oct 02 '17 at 16:28
My abstracted example might be sort of misleading,In total there are 23 columns,I need to arrange them in a desired sequence,not in a alphabetical order. — Max, Oct 02 '17 at 16:30

score 3 · Accepted Answer · answered Oct 02 '17 at 17:35

How does this work for you? I tried with just four columns, and it worked for me.

Sub rearrange_Columns()
Dim correctOrder() As Variant
Dim lastCol As Long
Dim headerRng As Range, cel As Range
Dim mainWS As Worksheet

Set mainWS = ActiveWorkbook.Worksheets("Sheet1")

' Edit this to be the correct order you need
correctOrder() = Array("Column A", "Column B", "Column C", "Column D")
' Now, we know that the number of headers you have to rearrange are `UBound(CorrectOrder)+1`


With mainWS
    lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    Set headerRng = .Range(.Cells(1, 1), .Cells(1, lastCol))
End With

Dim newWS As Worksheet
Set newWS = ActiveWorkbook.Sheets.Add
newWS.Name = "Rearranged Sheet"

Dim col As Long
With newWS
    For col = 1 To lastCol
        For Each cel In headerRng
            If cel.Value = correctOrder(col - 1) Then
                mainWS.Columns(cel.Column).Copy .Columns(col)
                Exit For
            End If
        Next cel
    Next col
End With

End Sub

Note: There's no real error handling, so if your headers need to be TRIM()ed or checked for erroneous info, you'll need to add something that does that.

@Max - Yay! Glad to hear it helped. (You can mark it as the Answer by clicking the check mark left of the post, below the up/down arrows) — BruceWayne, Jan 26 '18 at 15:04

How to rearrange the excel columns by the columns header name

1 Answers1

Linked