Re-order Columns based on header values VBA

Question

I have a worksheet where the I have a constant list of headers, though the column position changes all the time.

Below are the headers in the required order, and I'm trying to write a macro that will put them in a consistent order, but really struggling.

I've tried to find the correct column based on the name, but can't for the life of me get it to cut the column before I paste it elsewhere.

If anyone is able to help in any way that would be super appreciated.

Dim rngHeaders As Range
Dim rngAddressLine1Header As Range
Dim rngCityHeader As Range
Dim rngZipCodeHeader As Range
Dim rngPhoneHeader As Range
Dim rngCountryCodeHeader As Range
Dim rngSizeHeader As Range
Dim rngAddressLine2Header As Range
Dim rngStateHeader As Range
Dim rngAddressLine3Header As Range
Dim rngRafflenNameHeader As Range

  
Set ws = Sheets("Addresses")
With ws

Set rngHeaders = Range("1:1") 
Set rngAddressLine1Header = rngHeaders.Find("AddressLine1")
'Set rngCityHeader = rngHeaders.Find("City")
'Set rngZipCodeHeader = rngHeaders.Find("ZipCode")
'Set rngPhoneHeader = rngHeaders.Find("Phone")
'Set rngCountryCodeHeader = rngHeaders.Find("CountryCode")
'Set rngSizeHeader = rngHeaders.Find("Size")
'Set rngAddressLine2Header = rngHeaders.Find("AddressLine2")
'Set rngStateHeader = rngHeaders.Find("State")
'Set rngAddressLine3Header = rngHeaders.Find("AddressLine3")

.Select
    Column.rngAddressLine1Header.Select
    Selection.Cut
    Columns("D:D").Select
    Selection.paste

Thanks

following the below suggestion I have got it to work for the first column, though I get a 438 error after pasting the first column and it tries the second instance

.Select
    rngAddressLine1Header.EntireColumn.Cut
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Paste
    rngCityHeader.EntireColumn.Cut
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Paste```

Do I need to do a new Select function between each one?

`rngAddressLine1Header.entirecolumn.cut` but always check first the value is found to avoid RTEs. — SJR, Sep 04 '20 at 11:11
any ideas how I can fix the next iteration though please @SJR — RandomJones, Sep 04 '20 at 12:55
First you should read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — SJR, Sep 04 '20 at 12:58
It's worth spending time trying to understand as it will make your life much easier, VBA-wise at least. — SJR, Sep 04 '20 at 13:32

Re-order Columns based on header values VBA

Thanks

0 Answers0