Sort columns in a predetermined order bases on headers (not alphabetic)

Daniel,

or use

Public Sub sortOnHeaderName()

'#
'# declare private variables
'#
   Dim pvt_var_CustomList(1 To 3) As Variant
   
'#
'# populate the array with the header names in the desired sorting order
'#
   pvt_var_CustomList(1) = "Header A"
   pvt_var_CustomList(2) = "Header B"
   pvt_var_CustomList(3) = "Header C"

'#
'# create a custom sort list for the header names using the array as input
'#
   Application.AddCustomList pvt_var_CustomList

'#
'# now sort the data horizontally using the row that contains the header names as the
'# sort argument - in this example the header names are shown in cells A1 to C1
'#
   With ThisWorkbook.Worksheets("Sheet1").Sort
   
      .SortFields.Clear
      .SortFields.Add Key:=Range("A1:C1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:=Application.CustomListCount
            
      .SetRange Range("A1").Resize(3, 3)
      .Header = xlNo
      .MatchCase = False
      .Orientation = xlLeftToRight
      .SortMethod = xlPinYin
      .Apply
      
   End With
   
'#
'# delete the last custom list added (i.e. the one we created), thus always
'# the last one existing
'#
   Application.DeleteCustomList Application.CustomListCount

End Sub

Using your example data the code would look like

Public Sub sortOnHeaderName()

'#
'# declare private variables
'#
   Dim pvt_var_CustomList(1 To 6) As Variant
   
'#
'# populate the array with the header names in the desired sorting order
'#
   pvt_var_CustomList(1) = "Food"
   pvt_var_CustomList(2) = "Computers"
   pvt_var_CustomList(3) = "Shoes"
   pvt_var_CustomList(4) = "Shirts"
   pvt_var_CustomList(5) = "Candy"
   pvt_var_CustomList(6) = "Towels"

'#
'# create a custom sort list for the header names using the array as input
'#
   Application.AddCustomList pvt_var_CustomList

'#
'# now sort the data horizontally using the row that contains the header names as the
'# sort argument - in this example the header names are shown in cells A1 to F1
'#
   With ThisWorkbook.Worksheets("Sheet1").Sort
   
      .SortFields.Clear
      .SortFields.Add Key:=Range("A1:F1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:=Application.CustomListCount
            
      .SetRange Range("A1").Resize(3, 6)
      .Header = xlNo
      .MatchCase = False
      .Orientation = xlLeftToRight
      .SortMethod = xlPinYin
      .Apply
      
   End With
   
'#
'# delete the last custom list added (i.e. the one we created), thus always
'# the last one existing
'#
   Application.DeleteCustomList Application.CustomListCount

End Sub

OllieB

What if i need 47 headers?

I understand what to do with everuthing except for

.SetRange Range("A1").Resize(3, 6)

And the files i am going to use this on will have different ranges

The .SetRange is the instruction that is used to specify the area (= range) to be sorted.

So you will need to change that statement to reflect the area you want to be sorted on your data worksheet.

In the example; the value 3 indicates the number of columns to be included (should thus become 47) and the value 6 should reflect the number of data rows on your worksheet (including the header row).

You can also use something like (assuming the row number of the last data row is 101)

.SetRange Range("A1:AU101")

Yes you can. Many different possibilities. For example

dim lngLastRow as Long

lngLastRow = Thisworkbook.worksheets("Sheet1").UsedRange.Rows.Count

...

.SetRange Range("A1:AU" & lngLastRow)

or

.SetRange Range("A1").Resize(lngLastRow+1,47)

and where does it say that? (is the worksheet in question perhaps not called Sheet1?)