Kerry H
04-12-2019, 07:37 AM
Hi all, Hope you can help with the following. It is a section of a long Sub. At the beginning of the Sub, a range "RangeR" is read in. It is an entire worksheet which will later be sorted in the code below. There are 5 sorts being carried out following some copy/paste operations. The copy/paste is from WS 1 to WSs 2 to 5. In sort Case 3 (below), WS 1 is sorted and printed and then meant to be returned to its original state. RangeR represents the original state of WS 1. By trial and error, I have determined that RangeR retains its original values throughout all the previous operations of the Sub. However, at the end of the sort below, which works just fine, RangeR has taken on the values of the sorted Range1. (Range1 is also the entire original matrix of WS 1 and is changed by the sort. During the Sub, Range1 takes on various values, depending on the WS being pasted or sorted.) I have left out the print statements to test the Sub. Thank for your insights.
Case 3 ' Sort by Retail type (alpha), Name, Value in RETAILERS onl y
' RangeR ok at this point
ActiveWorkbook.Worksheets(Sh).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(Sh).Sort.SortFields.Add2 Key:=Range5, SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
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 = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' At this point, RangeR has taken on the values of the sorted "Range1".
With ActiveWorkbook.Worksheets(Sh)
RangeR.Copy
Worksheets(Sh).Cells(r1, c1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Case 3 ' Sort by Retail type (alpha), Name, Value in RETAILERS onl y
' RangeR ok at this point
ActiveWorkbook.Worksheets(Sh).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(Sh).Sort.SortFields.Add2 Key:=Range5, SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
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 = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' At this point, RangeR has taken on the values of the sorted "Range1".
With ActiveWorkbook.Worksheets(Sh)
RangeR.Copy
Worksheets(Sh).Cells(r1, c1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With