Using VBA to Sort A Named Range

[Chris Waller]

Dear All,

I am trying to create some VBA that will enable me to sort a number of Spreadsheets that come to me each month. The Spreadsheets are all in the same layout, however, the length of the Spreadsheets vary from only a couple of rows to several hundred rows. The problem is that the code below which I recorded refers to specific rows. From what I have read, it appears that I need to use a named range to facilitate the varying number of rows. The problem is that I don't know how to modify the code below to do this. I would be grateful if someone could look at the code and let me know where I have to change this. TIA

Sub Sort_By_Date_and_Name()
'
' Macro2 Macro
'

'
Application.Goto Reference:="R2C7"
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
ActiveWorkbook.Names.Add Name:="MyData", RefersToR1C1:= _
"=Output!R2C1:R109C7"
ActiveWorkbook.Worksheets("Output").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Output").Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, -5).Range("A1:A108"), Sorton:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Output").Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, -6).Range("A1:A108"), Sorton:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Output").Sort
.SetRange ActiveCell.Offset(-1, -6).Range("A1:G109")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Chris Waller]

adkock,

I tried the ammendment to the code you suggested above and it does work. That's brilliant. Thanks again.