Following code hangs up on .Apply. Can you see why? Thanks very much.
‘ All variables have been defined and appear to have the correct values at this point.
' I have copy and pasted Range1, Range2, etc. to check their contents. And all looks good.
With ActiveWorkbook.Worksheets(sh)
Set Range1 = .Range(.Cells(r1, c1), .Cells(rL, cL)) ' Entire range to be sorted
Set Range2 = .Range(.Cells(r1, c2), .Cells(rL, c2)) ' supply code column
Set Range3 = .Range(.Cells(r1, c3), .Cells(rL, c3)) ' retailer name column
Set Range4 = .Range(.Cells(r1, c4), .Cells(rL, c4)) ' face value column
Set Range5 = .Range(.Cells(r1, cT), .Cells(rL, cT)) ' Type of retailer
End With
Select Case S ' set to 1
Case 1 ' Sort by Name, Value only
ActiveWorkbook.Worksheets(sh).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(sh).Sort.SortFields.Add2 Key:=Range3,
SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets(sh).Sort.SortFields.Add2 Key:=Range4,
SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(sh).Sort
.SetRange Range1
.Header = xlNo
.MatchCase = False
.Orientation = orient
.SortMethod = xlPinYin
.Apply
End With
‘ All variables have been defined and appear to have the correct values at this point.
' I have copy and pasted Range1, Range2, etc. to check their contents. And all looks good.
With ActiveWorkbook.Worksheets(sh)
Set Range1 = .Range(.Cells(r1, c1), .Cells(rL, cL)) ' Entire range to be sorted
Set Range2 = .Range(.Cells(r1, c2), .Cells(rL, c2)) ' supply code column
Set Range3 = .Range(.Cells(r1, c3), .Cells(rL, c3)) ' retailer name column
Set Range4 = .Range(.Cells(r1, c4), .Cells(rL, c4)) ' face value column
Set Range5 = .Range(.Cells(r1, cT), .Cells(rL, cT)) ' Type of retailer
End With
Select Case S ' set to 1
Case 1 ' Sort by Name, Value only
ActiveWorkbook.Worksheets(sh).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(sh).Sort.SortFields.Add2 Key:=Range3,
SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets(sh).Sort.SortFields.Add2 Key:=Range4,
SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(sh).Sort
.SetRange Range1
.Header = xlNo
.MatchCase = False
.Orientation = orient
.SortMethod = xlPinYin
.Apply
End With
Last edited: