Rather than use VBA to open these files and extract the data I suggest that you use Power Query to do that part of the task - see
About Power Query in Excel[
^].
You can parameterise the Connections if there are going to be different file names - see
Parameters - Power Query | Microsoft Docs[
^]
As part of those queries you can order any of the columns any way you like.
To get this to run on opening add a call into the code for ThisWorkBook to refresh the queries e.g.
Private Sub Workbook_Open()
ThisWorkbook.RefreshAll
MsgBox "Data has refreshed"
End Sub
An alternative as suggested by Richard MacCutchan is to ensure the "Developer" tab is visible in the Ribbon and choose Macros, Record Macro. You can then record all the steps you use while doing this manually.
Once you have recorded everything (and I recommend that you do that in a series of shorter steps rather than attempting to do everything all at once) you should examine the "code" generated and tidy it up.
For example DO NOT use stuff like this
Range("A1:E90").Select
Selection.Copy
Windows("Book1").Activate
Range("A1").Select
ActiveSheet.Paste
Range("K24").Select
It is better to explicitly specify each range and then just assign the value from the source range to the value of the target range. E.g. that same code becomes
WorkBooks("Book1").Sheets(1).Range("A1:E90").Value = ThisWorkBook.Sheets(1).Range("A1:E90")
You can also tidy up all the extraneous code generated as the macro recorder will explicitly include code for all the default parameters. e.g. the sort might record as
Columns("A:F").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"D1:D4783"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:F4783")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
could be just
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("D1:D4783")
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:F4783")
.Apply
End With
Finally, where you see
ActiveWorkbook
change it to be the explicit reference to the workbook in question even if that is only
ThisWorkbook
Of course you could also use a mixture of the two techniques