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

WOW .. amazing again.
How do you come up with these things. Thank you so much.

Are you able to explain what this formula is doing?
1) the formula in the workbook looks a little different.
yours - =LET(z,VSTACK(Vehicles1:End!B2:B100000),SORT(UNIQUE(FILTER(z,z<>""))))
WrkBk - =LET(z,_xlfn.VSTACK(Vehicles1:End!B2:B100000),SORT(UNIQUE(FILTER(z,z<>""))))

2) it works as is but then I try to edit it or use it elsewhere it breaks it, just copy. Name error now.

Thank you for the Help.

Gray