CODEWITHSUNDEEP
excelvba
Max
Max

Reputation: 23

How to rearrange the excel columns by the columns header name

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.

Upvotes: 2

Views: 6233

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

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.

enter image description here

Upvotes: 3

Related Questions