After data refresh sort column A alphabetically

Question

I am having a little issue with my excel spreadsheet, I am hoping someone can help. I have Names starting in Cell=A7 that go to A177. All the other information are in Columns (B:H). Ideally I would like to run the code after I hit a Data Refresh. I am using this sheet so I can lookup information for another sheet, hence why it needs to be in Alphabetical Order A-Z. Information is from a web query.

Answer 1

EDIT:

Add the following code to a standard Module. See the edit in the code to make sure that it properly sorts all of your columns together as expected.

Option Explicit

dim DataTable as New Class1

Sub Auto_Open()
'This will run automatically when the workbook is opened, so macros will need to be enabled. 

'Select any cell in the data table.
Activesheet.Range("A7").Select
Set DataTable.qt = ThisWorkbook.ActiveSheet.QueryTables(1)
Sub

Sub AutoSort()
Dim rng As Range
Dim Nrow As Integer
Dim Ncol As Integer
Dim WS As Worksheet

'Assume that the active sheet contains the data that you want to sort
'Since it sounds like you'll be calling this from another macro, this
'is probably not a good assumption.
Set WS = ActiveWorkbook.ActiveSheet

'Get the row number of your last entry in column A, then the right most
'column of your data. Assume there is no other data on this worksheet.
Nrow = WS.Cells(Rows.Count, "A").End(xlUp).Row '177 in your case

'Replace the following line
'Ncol = WS.Cells(1, Columns.Count).End(xlToLeft).Column '8 in your case
'with this line
Ncol = WS.Cells(7, Columns.Count).End(xlToLeft).Column '8 in your case

'set all of your data in a range.
Set rng = WS.Range(Cells(7, 1), Cells(Nrow, Ncol))

'the actual sorting
rng.Sort key1:=rng, order1:=xlAscending, Header:=xlYes

End Sub

Then create a class module and insert the following code:

Option Explicit

Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)

    If Success = True Then

        Call Module1.AutoSort
        MsgBox "Data was updated and sorted."

    End If

End Sub

Note that Auto_Open() must be ran first in order for AutoSort() to work properly. This should happen any time you open the workbook.

The code is drawing heavily upon the work of others, namely:
How to call macro after Refresh or Refresh All button pressed? answered by @Rory

Microsoft Documentation