VBA - Sorting Multiple Columns by Header's Names

[AndrewBR]

Hi Guys! I have the code below to sort my table, but this sheet is downloaded from a system and the columns change position from time to time.

Is there a way to execute the code below using header's names?

Range("A1").CurrentRegion.Sort _
   key1:=Range("AY1"), Order1:=xlAscending, _
   key2:=Range("AZ1"), order2:=xlAscending, _
   key3:=Range("BV1"), order3:=xlAscending, _
   Header:=xlYes

Like this:

Range("A1").CurrentRegion.Sort _
   key1:=Range("[B]CPO Number[/B]"), order1:=xlAscending, _
   key2:=Range("[B]CPO Item[/B]"), order2:=xlAscending, _
   key3:=Range("[B]Invoice Number[/B]"), order3:=xlAscending, _
   Header:=xlYes

I've been reading this forum for a long time, but this is my first post!


I'd a research and haven't find anything.


I really appreciate any help you can provide.

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub cols()
   Dim Fnd(1 To 3) As Range
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("CPO Number", "CPO Item", "Invoice Number")
   For i = 1 To 3
      Set Fnd(i) = Range("1:1").find(Ary(i - 1), , , xlWhole, , , False, , False)
   Next i
   Range("A1").CurrentRegion.Sort _
   key1:=Fnd(1), order1:=xlDescending, _
   key2:=Fnd(2), order2:=xlAscending, _
   key3:=Fnd(3), order3:=xlAscending, _
   header:=xlYes
      
End Sub

 

[AndrewBR]

That's great!! It's working like a charm!! Thank's a lot!

 

[Fluff]

Glad to help & thanks for the feedback