Sorting Master Data Services (MDS) entity table with VBA

[StringKing]

Good day,

I am trying to sort a table that updates from Master Data Services add-in on Excel, by using VBA.

I cannot reference the table (ListObject) name, as the name might change everytime the table gets updated when a connection is established.
The name of the sheet on which the table is is also variable and can also change everytime someone establishes a connection on the MDS sheet.

I have tried sorting using the sheet and listobject names, as well as referring to it at Sheets(1).ListObject(1).

When stepping through the code in break mode (F8) the VBA executes perfectly, but when running the code (F5) it does not execute the sort.

Please help so that I can execute the macro without having to step through it in break mode and in order for me to add the rest of the VBA code to complete the remainder of the requirement.

'--__--__--__--__--__--__--__--__--__--

Sub SortNoRange()


Dim Ws As Worksheet
Dim LR As Long


'Sort MDS data
Sheets(1).Activate


Set Ws = ActiveSheet
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row


Ws.Sort.SortFields.Clear
Ws.Sort.SortFields.Add Key:=Range("X3:X" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Ws.Sort
.SetRange Range("A3:X" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

'--__--__--__--__--__--__--__--__--__--

 

[StringKing]

Unfortunately I cannot supply the Excel workbook, but see below screen snip: