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...

 

[VoG]

Try

    .Protect AllowFiltering:=True, Password:="pw"

 

[Kentetsu]

Thank you Sir...

 

[jcarroll01]

Hi...I used the above code with some tweaks to sort one sheet on open....if I needed to replicate sorting other sheets, one after another, in the same workbook, what steps would I add? Duplicating the code for an additional sheet doesn't seem to work, but maybe I am missing something?

The simple code from 3/31 worked for me if it makes it easier.

Thanks

 

[VoG]

Try this

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Range("A4:CI301").Sort Key1:=Range("C4"), Order1:=xlDescending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Next ws
End Sub

 

[jcarroll01]

I left out the part that each WS has different sort parameters. Is there an easy way to copy the code properly and just adjust the parameters contained within?

There are only 5 or 6 sheets to sort, so I don't mind the code..

Thanks for the answer though. Next time I'll try to be consistent and use that to my advantage.

 

[VoG]

Can you list the sheet names, sort ranges and key cell for the sort.

 

[jcarroll01]

WK - sorting a4:D200 - based on D4
PH - sorting A4:D200 - based on D4
CM - sorting A4:D200 - based on D4
Tardy - sorting Whole sheet starting at C2....only doing this because the # of columns and rows changes. Based on G2
Reason - same sort as Tardy, based on D2
Adherence - same as above, based on E2
Lunch adherence - same as above, based on E2.

Hope this helps.

Thanks, Jon

 

[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", "*", "*", "*", "*")
KyAr = Array("D4", "D4", "D4", "C2", "D2", "E2", "E2")
For i = LBound(ShAr) To UBound(ShAr)
    If RgAr(i) = "*" 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]

Run-time error '1004':
Application-defined or object defined error

I suppose this is a bad time to tell you that these sheets are not the only sheets in this workbook?

I'm not sure if this is the source of the problem, but it occurs to me that I didn't tell you.

The debug keys in on the section under the "Else" statement.

 

[VoG]

When you get this error click on Debug - which line of code is highlighted?