VBA: Dynamic List from same column on multiple sheets

For Excel Insider try

=LET(z,VSTACK(Vehicles1:End!B2:B100000),SORT(UNIQUE(FILTER(z,z<>""))))

or Power Query
Change file path in red

let
    Source = Excel.Workbook(File.Contents("D:\ForRickv2.xlsx"),null, true),
    Filtered = Table.SelectRows(Source, each Text.StartsWith([Name], "Vehicles")),
    RemoveHeader = Table.AddColumn(Filtered, "RemoveH", each Table.Skip([Data],1)),
    Combine = Table.Combine(RemoveHeader[RemoveH]),
    #"Removed Duplicates" = Table.Distinct(Combine),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Column1", Order.Ascending}})
in
    #"Sorted Rows"

Watch this for Power Query
https://youtu.be/KfuYxBDBkAo