Selecting an active worksheet AND an active table that keep changing names!

In a project I am working on I import a sheet with a pivot table from another workbook.

I then select a "Grand Total" cell and "explode" it, which creates a new sheet with all the raw data on it.

I need to work with that.

At first from a recorded macro it was:

'    ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table2").Sort.SortFields.Add _
'        Key:=Range("Table2[PersonName]"), SortOn:=xlSortOnValues, Order:= _
'        xlAscending, DataOption:=xlSortNormal

And I now have tweaked it to:

'    ActiveSheet.ListObjects(1).Sort.SortFields.Add _
'        Key:=Range("Table2[PersonName]"), SortOn:=xlSortOnValues, Order:= _
'        xlAscending, DataOption:=xlSortNormal

which got me past 1) The fact that every time I import a revised pivot table in and expand it, "Sheet1" gets a brand new incremental "Sheet<x>" name given to it ("Sheet1", "Sheet2","Sheet3",...) and got me past the first Table2 reference that was in there, but I believe that second one - the one that is in Range("Table2[PersonName]") is causing me to crash with:

Run-time error '1004':
Method 'Range' of object '_Global' failed

I am creating by trial and error, so please do not presume I know what I am doing!

It will be followed by:

   ActiveSheet.ListObjects(1).Sort.SortFields.Add _
        Key:=Range("Table2[PersonName]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal

'Set sort on SurveyDate
    ActiveSheet.ListObjects(1).Sort.SortFields.Add _
        Key:=Range("Table2[TestDate]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortTextAsNumbers

'SORT---
    With ActiveSheet.ListObjects(1).Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

So if you see anywhere where I might run into another train wreck, please warn me!

Why instead of a table you just use ranges? That would be a good idea. So instead of :

'Set sort on SurveyDate
    ActiveSheet.ListObjects(1).Sort.SortFields.Add _
        Key:=Range("Table2[TestDate]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortTextAsNumbers

Something like this

'Set sort on SurveyDate
    ActiveSheet.ListObjects(1).Sort.SortFields.Add _
        Key:=Range("B2:B20"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortTextAsNumbers

I cannot help you more because I canno see your sheet. But try this. The code asumes your data is in column "B" and starts in row 1.

Sub Try()

Dim sh As Worksheet
Dim r As Range

Set sh = ActiveSheet
Set r = Range("B1:B" & sh.Cells(Rows.Count, "B").End(xlUp).Row)

' First delete any other sort
sh.Sort.SortFields.Clear

'Set up the sort
sh.Sort.SortFields.Add Key:=r, SortOn:=xlSortOnValues, _
                       Order:=xlAscending, _
                       DataOption:=xlSortTextAsNumbers


' Do the actual sort
    With sh.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Thanks