Sorting multiple sheets with variable rows

[peee2]

Hi All,
The following code has some issues.it is working when all rows are same but whenever it needs to sort a sheet with variable rows , it is selecting the cells but not sorting. Pls Help.

Sub Sort()




    Dim i As Long
    Dim LastARow As Integer
    LastARow = Range("A11").End(xlDown).Offset(-3, 0).Row
    
    
    For i = 14 To Worksheets.count
    Rows("11:11").Select
    Range(Selection, Selection.End(xlDown).Offset(-3, 0)).Select


        With Worksheets(i).Select
        With Worksheets(i).Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("C11:C" & LastARow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
        .SortFields.Add Key:=Range("A11:A" & LastARow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
        .SortFields.Add Key:=Range("B11:B" & LastARow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
 
        .SetRange Range("A11:IU" & LastARow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    
       End With
       End With
       
    Next
End Sub

 

[BlueHornet]

This seems to be a code sequencing problem. You might have assumed that the code will always start from a particular point (and at the With Worksheets(i).Select statement that point is set), but prior to that point, at the instant that your Range Selection is made ... where is the code operating from? It's from some place that may only coincidentally be correct ... and after the code starts running, it's not correct, because until you do your Worksheets(i).Select, you're operating at Worksheets(i‑1).

If you start the code running at Worksheets(i), then it seems to work properly for that worksheet, but if you start at some other worksheet with a row count different from Worksheet(i), then Worksheet(i) will also be performed incorrectly.

Select your worksheet before you count your End(xlDown).Rows count.

 

[peee2]

Ok..will select the sheet from where i want to start the sorting and try running it..but den doesnt i=14 means it will start from 14th sheet in the workbook

 

[BlueHornet]

As you've got it written, yes, code operation will start from sheets(14). But why did you write it so if you didn't want it to start from there? It's also possible to keep the code as it is and to start exactly how you are, BUT without telling it explicitly where to start, you're at the mercy of wherever the focus happens to be when the code does start to operate. (Which may not always be up to you, if the file will be used by others or if some other procedure calls the code from another location.)

In any case, when you've selected a new Worksheet to begin operation, that's when you'll want to count the rows / range over which to operate, not before you get there and examine it.

 

[Fluff]

How about

Sub Sort()
   Dim i As Long
   Dim LastARow As Integer
   
   
   For i = 14 To Worksheets.Count
      With Worksheets(i)
         LastARow = .Range("A11").End(xlDown).Offset(-3, 0).Row
         .Rows("11:11").Select
         .Range(Selection, Selection.End(xlDown).Offset(-3, 0)).Select
         With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("C11:C" & LastARow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                  xlSortNormal
            .SortFields.Add Key:=Range("A11:A" & LastARow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
                  xlSortNormal
            .SortFields.Add Key:=Range("B11:B" & LastARow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                  xlSortNormal
   
            .SetRange Range("A11:IU" & LastARow)
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
         End With
      End With
   Next i
End Sub

 

[peee2]

Sir i wanted to start sorting from my 14th sheet only....anyways will try n make the changes as per your advice..thanks

 

[peee2]

..will let you know as soon as i try it..thanks

 

[peee2]

no..its not working. gives a message saying run-time error 1004: Select method of range class failed

 

[peee2]

As Bluehornet had suggested..just selected the sheet and now the code seems to be working fine.Thanks Bluehornet and fluff