Sort page on open?

Kentetsu

Well-known Member
Joined
Jan 22, 2004
Messages
520
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...
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Try this: right click the Excel logo just to the left of File on the menu bar, select View Code and paste in

Code:
Private Sub Workbook_Open()
Sheets("Summary").Range("A4:CI301").Sort Key1:=Range("C4"), Order1:=xlDescending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
 
Upvote 0
Try this, change pw to suit

Rich (BB code):
Private Sub Workbook_Open()
With Sheets("Summary")
    .Unprotect Password:="pw"
    .Range("A4:CI301").Sort Key1:=Range("C4"), Order1:=xlDescending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    .Protect Password:="pw"
End Sub
 
Upvote 0
Thank you Peter. I inserted the code, but I'm getting an error;

"Compile Error: Expected end with"

Does that mean anything to you?
 
Upvote 0
Sorry, my error

Code:
Private Sub Workbook_Open()
With Sheets("Summary")
    .Unprotect Password:="pw"
    .Range("A4:CI301").Sort Key1:=Range("C4"), Order1:=xlDescending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    .Protect Password:="pw"
End With
End Sub
 
Upvote 0
Sorry to bother you again, but I've found another snag. Apparently when the code re-protects the sheet it kills the auto filter function. I've tried resetting the function, but once I reopen the spreadsheet it is again disabled.

Is there a way to return that function?

Thanks...
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top