Loop a sort function

[james.L]

I am trying to run a sort function many times across many ranges but I cannot seem to get the second iteration of the loop to work properly

First data range that needs to be sorted is B108:E126, sort on column E. Next range is H108:K126, sort on column k- offset by 6 columns from the first range

Sortfluxcriteria is the column I'm sorting and sortflux is the total range. The first iteration sorts fine but I can't seem to get the ranges defined correctly so that they move correctly to the second iteration. It completes with the first cycle and does nothing after that or just sorts on the original range twice. Any advice?

Here is my code thus far -

Sub Macro3()
Dim j As Integer
    j = 0

    Do While j < 12
        Dim sortfluxcriteria As Range
        Set sortfluxcriteria = Range("E108:E126").Offset(0, j)
        Dim sortflux As Range
        Set sortflux = Range("B108:E126").Offset(0, j)

        ActiveSheet.sort.SortFields.Clear
        ActiveSheet.sort.SortFields.Add2 Key:=sortfluxcriteria _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    
        With ActiveSheet.sort
               .SetRange sortflux
               .Header = xlNo
               .MatchCase = False
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
                .Apply
        End With

        j = j + 6
    Loop
    
End Sub

[p45cal]

All works here as expected.
Is there something changing the active sheet which we're not seeing?

[james.L]

There is plenty of data around it so I'm not sure how to answer your question. Thanks for the response regardless!

I ended up reworking the loop to have it run a certain number of times instead of conditional and that did the trick.

Sub macro1 ()
Dim m As Long
Dim iterations As Integer
'Iterations defined through a user input box
For m = 0 To iterations
Dim sortfluxcriteria As Range
Set sortfluxcriteria = Activesheets.Range("E108:E126").Offset(0, m * 6)
Dim sortflux As Range
Set sortflux = Activesheets.Range("B108:E126").Offset(0, m * 6)
Activesheets.sort.SortFields.Clear
Activesheets.sort.SortFields.Add2 Key:=sortfluxcriteria _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With Activesheets.sort
.SetRange sortflux
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next m
End sub