dynamic data changes

[DSCHROE67]

I have the following set of code to create a query based on a table range. The rows should never change but the columns could change in amount and the values within the columns could change. In the 2nd paragraph of this macro after creating the ranges, each value 6204, 6106, etc is a unique store number. sores can be added or removed on a month to month basis. The sore list list file is created using the "unique" Excel function from another table. Since the macro below has a defined number and set of stores it does not change if the actual data in the "unique" formula changes. Is there a way to make this routine dynamic to pick up each new store or remove the dropped off store from the "unique" formula processes? The criteria "column1, clomun2 etc at the end of the formula was my attempt to leave space for additional store criteria and are not really necessary if the formula is dynamic and only picks up the new store range from the unique function. I created this initially from the record macro button so I am not really familiar with the context of the code. Here is the unique function if you would like to see it: =TRANSPOSE(UNIQUE(SORT(FILTER(TEXT('BB Only'!H:H,"0"),(TEXT('BB Only'!H:H,"0")>"3800")*(TEXT('BB Only'!H:H,"0")<"3900")))))

Thanks for your help


Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").Name = "Table_FACTORY"
Sheets("RETAIL").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$X$5:$CH$50"), , xlYes).Name = _
"Table2"
Range("Table2[#All]").Select
ActiveSheet.ListObjects("Table2").Name = "Table_RETAIL"


ActiveWorkbook.Queries.Add Name:="Table_RETAIL", formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""Table_RETAIL""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Brooks Store #"", type datetime}, {""6204"", type number}, {""6106"", type number}, {""6006"", type number}, {""6108"", type number}, {""6009"", type number}, {""6207"", type number}, {""6111"", type number}, {""6013"", type num" & _
"ber}, {""6016"", type number}, {""6213"", type number}, {""6118"", type number}, {""6126"", type number}, {""6127"", type number}, {""6021"", type number}, {""6133"", type number}, {""6123"", type number}, {""6024"", type number}, {""6025"", type number}, {""6026"", type number}, {""6027"", type number}, {""6128"", type number}, {""6136"", type number}, {""6030"", t" & _
"ype number}, {""6031"", type number}, {""6137"", type number}, {""6033"", type number}, {""6036"", type number}, {""6140"", type number}, {""6184"", type number}, {""6135"", type number}, {""6044"", type number}, {""6052"", type number}, {""6154"", type number}, {""6160"", type number}, {""6062"", type number}, {""6164"", type number}, {""6065"", type number}, {""60" & _
"69"", type number}, {""6070"", type number}, {""6071"", type number}, {""6073"", type number}, {""6075"", type number}, {""6078"", type number}, {""6079"", type number}, {""6081"", type number}, {""6084"", type number}, {""6191"", type number}, {""6195"", type number}, {""6097"", type number}, {""Column1"", Int64.Type}, {""Column2"", Int64.Type}, {""Column3"", Int64" & _
".Type}, {""Column4"", Int64.Type}, {""Column5"", Int64.Type}, {""Column6"", Int64.Type}, {""Column7"", Int64.Type}, {""Column8"", Int64.Type}, {""Column9"", Int64.Type}, {""Column10"", Int64.Type}, {""Column11"", Int64.Type}, {""6099"", Int64.Type}, {""6050"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""


Workbooks("ChannelRESTATE.xlsx").Connections.Add2 "Query - Table_RETAIL", _
"Connection to the 'Table_RETAIL' query in the workbook.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table_RETAIL"";Extended Properties=""""" _
, "SELECT * FROM [Table_RETAIL]", 2
Sheets("FACTORY (2)").Select