Sort columns using variables

[lilmisspink07]

Hello there,

I need to sort by multiple columns in a worksheet where the worksheet name, column locations and used range will change every time. I have the below code but it's not sorting so I know that I've missed something.... I think it might be the range variables that I set and then use as the keys?
Any advice would be much appreciated!

[SIZE=2]Dim MACHINE As Range
Set MACHINE = Range(Range("A1:DZ1").Find("MACHINE"), Range("A1:DZ1").Find("MACHINE").EntireColumn)

Dim MESSAGE_ID As Range
Set MESSAGE_ID = Range(Range("A1:DZ1").Find("MESSAGE_ID"), Range("A1:DZ1").Find("MESSAGE_ID").EntireColumn)

With ActiveWorkbook.ActiveSheet.Sort
    With .SortFields
        .Clear
        .Add Key:=MACHINE, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add Key:=MESSAGE_ID, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
    .SetRange Range("A1").CurrentRegion
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With[/SIZE]

 

[RickXL]

Something needs to be selected:

    Dim MACHINE As Range
    Set MACHINE = Range(Range("A1:DZ1").Find("MACHINE"), Range("A1:DZ1").Find("MACHINE").EntireColumn)
    
    Dim MESSAGE_ID As Range
    Set MESSAGE_ID = Range(Range("A1:DZ1").Find("MESSAGE_ID"), Range("A1:DZ1").Find("MESSAGE_ID").EntireColumn)

    Range("A1").Select

    With ActiveWorkbook.ActiveSheet.Sort
        With .SortFields
            .Clear
            .Add Key:=MACHINE, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=MESSAGE_ID, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        End With
        .SetRange Range("A1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With