I used Macro recording to get the code to sort a large sheet that I sort often. I used it for a while and it worked just fine. Unfortunately, I rearranged the sheet, ran the macro and becuase of the fixed ranges in the macro, it sorted one part of the sheet but not the other cells so now the values in one column are associated with the incorrect rows.
To prevent this from happening again, I want to set the entire sheet as the range. For the life of me, and after several days off and on of messing with it, I can't figure out to do this. Below is the base code and where I am having difficulty.
Any help would be greatly appreciated!!!!
Current Macro
Sub Sort_Master_To_Do_List()
'
' set active cell to recall at end
strCellNow = ActiveCell.Address
' initilize sort
ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort.SortFields.Clear
' SORT FIELD: Close Date
ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("I2:I5000"), SortOn:=xlSortOnCellColor, Order:=xlAscending, _
DataOption:=xlSortNormal
' SORT FIELD: Pri
ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("A2:A5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
' SORT FIELD: Area
ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("E2:E5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
' SORT FIELD: Description
ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("F2:F5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
' execute sort
With ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort
.SetRange Range.Cells.Select <<<<<<<<<<<---- this ie the code line that does not work. I have done lots of variations on this with no luck
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' select all cells and autufit row height
Cells.Select
Selection.Rows.AutoFit
' restore active cell before sorting was done
Range(strCellNow).Select
End Sub
To prevent this from happening again, I want to set the entire sheet as the range. For the life of me, and after several days off and on of messing with it, I can't figure out to do this. Below is the base code and where I am having difficulty.
Any help would be greatly appreciated!!!!
Current Macro
Sub Sort_Master_To_Do_List()
'
' set active cell to recall at end
strCellNow = ActiveCell.Address
' initilize sort
ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort.SortFields.Clear
' SORT FIELD: Close Date
ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("I2:I5000"), SortOn:=xlSortOnCellColor, Order:=xlAscending, _
DataOption:=xlSortNormal
' SORT FIELD: Pri
ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("A2:A5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
' SORT FIELD: Area
ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("E2:E5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
' SORT FIELD: Description
ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("F2:F5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
' execute sort
With ActiveWorkbook.Worksheets("MASTER").AutoFilter.Sort
.SetRange Range.Cells.Select <<<<<<<<<<<---- this ie the code line that does not work. I have done lots of variations on this with no luck
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' select all cells and autufit row height
Cells.Select
Selection.Rows.AutoFit
' restore active cell before sorting was done
Range(strCellNow).Select
End Sub