Sorting Columns via headers.

[Rado88]

Hi All,

I've a query regarding sorting columns via headers in few sheets of one workbook. The data to sheets 2 + will be copied daily but with same columns (in different order).

I've a workbook with few sheets full of data, and all have same columns in different order (for example column C from sheet 1 is column Z (with different data in rows) in sheet 2)). Sheet 1 has the correct order in which the fle should look to make other macros to work. There can be up to 3 - 4 sheets that need to be adjusted to sheet1.

I was wondering if it's possible to write a macro that would loop through column headers and move them to same order as in sheet1. I was thinking about using ListObject.HeaderRowRange Property but I'm not sure how to start. I know that the fastest solution would be to record a macro, but the columns end on BH or further column, and if the columns would be in different order then on the one used to record the macro this could cause issues.

Thank you in advance for any tips how to create such sorting tool.

 

[footoo]

Sub SortCol()
Dim master$, ray As Variant, ListNum%, sh As Worksheet
master = "Sheet1" 'Change to actual master sheet name
ListNum = Application.CustomListCount + 2
With Sheets(master)
    ray = .Range(.[A1], .Cells(1, Columns.Count).End(xlToLeft))
End With
Application.AddCustomList ray
For Each sh In Worksheets
    If sh.Name <> master Then _
        sh.Cells.Sort key1:=sh.Rows(1), Header:=xlNo, _
        Orientation:=xlLeftToRight, OrderCustom:=ListNum
Next
ListNum = ListNum - 1
Application.DeleteCustomList ListNum
End Sub