Sort page on open?

[Kentetsu]

Due to the protection function, users cannot sort by descending (using filter), and this is causing some problems. So I was wondering if it were possible for the sheet to be sorted automatically whenever the spreadsheet is opened. Is this a viable solution?

The sheet is titled Summary (Sheet 1), and the range is A4:CI301 and I would like to sort based on the data in column C, in Descending order (C4 - C301).

Like I said, I'm not sure if this is the best method or not, so I am open to any suggestions that may be applicable.

You input is appreciated. Thanks...

 

[jcarroll01]

The line directly under the "else" statement.

 

[VoG]

This might be because * is a wildcard - try

Private Sub Workbook_Open()
Dim ShAr, RgAr, KyAr
Dim i As Integer
ShAr = Array("WK", "PH", "CM", "Tardy", "Reason", "Adherence", "Lunch adherence")
RgAr = Array("A4:D200", "A4:D200", "A4:D200", "Z", "Z", "Z", "Z")
KyAr = Array("D4", "D4", "D4", "C2", "D2", "E2", "E2")
For i = LBound(ShAr) To UBound(ShAr)
    If RgAr(i) = "Z" Then
        Sheets(ShAr(i)).UsedRange.Sort Key1:=Range(KyAr(i)), Order1:=xlDescending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Else
        Sheets(ShAr(i)).Range(RgAr).Sort Key1:=Range(KyAr(i)), Order1:=xlDescending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End If
Next i
End Sub

 

[jcarroll01]

Sorry to get back so late...it still throws the same error in the same location:

Sheets(ShAr(i)).Range(RgAr).Sort Key1:=Range(KyAr(i)), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

 

[VoG]

Try

Private Sub Workbook_Open()
Dim ShAr, RgAr, KyAr
Dim i As Integer
ShAr = Array("WK", "PH", "CM", "Tardy", "Reason", "Adherence", "Lunch adherence")
RgAr = Array("A4:D200", "A4:D200", "A4:D200", "Z", "Z", "Z", "Z")
KyAr = Array("D4", "D4", "D4", "C2", "D2", "E2", "E2")
For i = LBound(ShAr) To UBound(ShAr)
    If RgAr(i) = "Z" Then
        Sheets(ShAr(i)).UsedRange.Sort Key1:=Range(KyAr(i)), Order1:=xlDescending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Else
        Sheets(ShAr(i)).Range(RgAr(i)).Sort Key1:=Range(KyAr(i)), Order1:=xlDescending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End If
Next i
End Sub

 

[jcarroll01]

Same error - same location.

It occurs to me...and it isn't my code or a skill I have, but how is your code determining the starting point for the sort on the non-ranged delimited sheets?

Example - My "tardy" sheet, if I manually sort it, requires that I choose A3bottom/right of the full range) currently AI224....but changes rows and columns, depending.

Thanks for the time and consideration.

 

[VoG]

I can't reproduce that error.

The ranges are the ones that you gave me in an earlier post.

 

[jcarroll01]

Maybe we got messages crossed:

I really could get along with the following, (using your code)

ShAr = Array("WK", "PH", "CM", "Tardy", "Reason", "Adherence", "Lunch adherence")
RgAr = Array("A4:D200", "A4:D200", "A4:D200", "C3:AV500", "A3:IV500", "A3:IV500", "A3:IV500")
KyAr = Array("D4", "D4", "D4", "G2", "D2", "E2", "E2")

I'm not sure how to manipulate the If-Then-Else to accomodate the change, or if it would even make a difference.

Thanks

 

[VoG]

I think I've found my error

Try

Sub test2()
Dim ShAr, RgAr, KyAr
Dim i As Integer
ShAr = Array("WK", "PH", "CM", "Tardy", "Reason", "Adherence", "Lunch adherence")
RgAr = Array("A4:D200", "A4:D200", "A4:D200", "Z", "Z", "Z", "Z")
KyAr = Array("D4", "D4", "D4", "C2", "D2", "E2", "E2")
For i = LBound(ShAr) To UBound(ShAr)
    If RgAr(i) = "Z" Then
        Sheets(ShAr(i)).UsedRange.Sort Key1:=Sheets(ShAr(i)).Range(KyAr(i)), Order1:=xlDescending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Else
        Sheets(ShAr(i)).Range(RgAr(i)).Sort Key1:=Sheets(ShAr(i)).Range(KyAr(i)), Order1:=xlDescending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End If
Next i
End Sub

 

[jcarroll01]

Success!!

I have tested and re-tested and I believe that I have exactly what I needed.

Thank you for your patience and tenacity.

Sincerely,

Jon