I have this code which works
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect "mark", UserInterfaceOnly:=True
Sheets("FOUNDATION").Select
ActiveWindow.DisplayGridlines = False
Rows("145:900").Select
Selection.EntireRow.Hidden = True
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollRow = 6
Range("A1:A5").Select
Range("A6:GD123").Select
Selection.Sort Key1:=Range("C6"), Order1:=xlDescending, Key2:=Range("D6") _
, Order2:=xlAscending, Key3:=Range("A6"), Order3:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
ActiveWindow.ScrollRow = 6
Range("A1:A5").Select
Next ws
Sheets("YEAR ONE").Select
ActiveWindow.DisplayGridlines = False
Rows("145:900").Select
Selection.EntireRow.Hidden = True
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollRow = 6
Range("A1:A5").Select
Range("A6:GD123").Select
Selection.Sort Key1:=Range("C6"), Order1:=xlDescending, Key2:=Range("D6") _
, Order2:=xlAscending, Key3:=Range("A6"), Order3:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
ActiveWindow.ScrollRow = 6
Range("A1:A5").Select
End Sub
When I add another worksheet to be sorted, as shown below it throws back a compile error, next without for. I'm very much a novice at vba and would really appreciate some pointers, have read various posts but can't work out why it does not work.
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect "mark", UserInterfaceOnly:=True
Sheets("FOUNDATION").Select
ActiveWindow.DisplayGridlines = False
Rows("145:900").Select
Selection.EntireRow.Hidden = True
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollRow = 6
Range("A1:A5").Select
Range("A6:GD123").Select
Selection.Sort Key1:=Range("C6"), Order1:=xlDescending, Key2:=Range("D6") _
, Order2:=xlAscending, Key3:=Range("A6"), Order3:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
ActiveWindow.ScrollRow = 6
Range("A1:A5").Select
Next ws
Sheets("YEAR ONE").Select
ActiveWindow.DisplayGridlines = False
Rows("145:900").Select
Selection.EntireRow.Hidden = True
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollRow = 6
Range("A1:A5").Select
Range("A6:GD123").Select
Selection.Sort Key1:=Range("C6"), Order1:=xlDescending, Key2:=Range("D6") _
, Order2:=xlAscending, Key3:=Range("A6"), Order3:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
ActiveWindow.ScrollRow = 6
Range("A1:A5").Select
Next ws
Sheets("YEAR TWO").Select
ActiveWindow.DisplayGridlines = False
Rows("145:900").Select
Selection.EntireRow.Hidden = True
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollRow = 6
Range("A1:A5").Select
Range("A6:GD123").Select
Selection.Sort Key1:=Range("C6"), Order1:=xlDescending, Key2:=Range("D6") _
, Order2:=xlAscending, Key3:=Range("A6"), Order3:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
ActiveWindow.ScrollRow = 6
Range("A1:A5").Select
End Sub
Bookmarks