My main workbook Analyzer.xlsm links to another workbook called DataSource.xlsx
DataSource.xlsx is regularly updated and replaced so it needs to be sorted each time.
Both workbooks are in the same folder/location
Could anyone help with VBA that would run from Analyzer.xlsm to open DataSource.xlsx, sort it in descending order based on column E, save it and close it. Note that Row 1 in DataSource is a header.
Here's what I have below, but I need to make it relative so it will open the DataSource no matter the folder they are both in and also need to make it relative with regards to sorting the entire worksheet which will have a different number of rows each time:
Any help is greatly appreciated!
DataSource.xlsx is regularly updated and replaced so it needs to be sorted each time.
Both workbooks are in the same folder/location
Could anyone help with VBA that would run from Analyzer.xlsm to open DataSource.xlsx, sort it in descending order based on column E, save it and close it. Note that Row 1 in DataSource is a header.
Here's what I have below, but I need to make it relative so it will open the DataSource no matter the folder they are both in and also need to make it relative with regards to sorting the entire worksheet which will have a different number of rows each time:
Code:
Sub Externalsort()
Workbooks.Open Filename:="C:\DataSource.xlsx"
Cells.Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("DataSource").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DataSource").Sort.SortFields.Add2 Key _
:=Range("E2:E11647"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("DataSource").Sort
.SetRange Range("B1:AM11647")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
Any help is greatly appreciated!