Results 1 to 4 of 4

Problem wih sort code on opening workbook

Threaded View

  1. #1
    Registered User
    Join Date
    03-13-2010
    Location
    Bedfordshire, England
    MS-Off Ver
    Excel 2010
    Posts
    60

    Problem wih sort code on opening workbook

    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
    Last edited by BobTheRocker; 11-05-2010 at 05:16 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1