Replacing the custom list in a sort with VBA

[Deleted]

Hello,

I want to create a macro to dynamically sort a range to match a manually sorted version of a list.

The Question: 

How do I get a custom list inserted into the below VBA code. Instead of getting an order from the list of custom orders I want to replace whats in quotes with logic to grab something from my worksheet. In this instance it is a list of numbers that has an undefined length so it would be great if the logic stopped adding things to the list once it found an empty cell. 

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A10:G10") _
, SortOn:=xlSortOnValues, Order:=xlAscending, _

CustomOrder:="1,4,5,6,3,2,7", _ --I want to replace this list with something dynamic from my sheet. 
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A10:G11")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
End Sub

Background:

There is a list of data points on the first sheet that dynamically turn into column headers on another sheet. 

I use offsets on the second sheet to turn the list into column headers which is great because those move when I reorder the manual list. However, I make some entries underneath the column headers that need to move around along with the headers when they change order.

Right now I have all the rows on the manually sorted numbered and that number is reordered whenever the list is. That number is also pulled over below the column header on the next sheet as an ID. Right below the Column ID row we have another set of IDs for the entries that need to move around to match the headers. When the manual list is reordered the two matching IDs are no longer over each other.

The list of IDs from the first table needs to be my sort order for the list of entry ID under the column headers. This will bring the two parts back into alignment after the manual list has been resorted.