Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    2003 vs 2007 macro functionality

    I have a Excel workbook with a dozen identically designed worksheets (columns, rows and headers), each named differently.

    I had created a 2003 Excel macro (using the macro recorder) which would sort the sheets by Col AQ, and I assigned the macro to a button on my tool-bar, and after populating the data on each tab, I'd click the button and the sheet would sort as designed.

    Work converted us to excel 2007 and now the macro will only work on the 1st tab of the workbook and not on the others.

    I assume this is because the tabs are differently named, how can I create a macro that will sort the sheets (as it worked in 2003) but not reference the worksheet name?

    Thanks
    Tom

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Tom,

    You could use ActiveSheet vs a sheet name. If you want it to sort all sheets at once you could use a For Each Sheet in Worksheets loop. If this doesn't work post your macro so we can see what is going on.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts
    The Activesheet worked great, I am curious how to do the entire workbook at once though..
    below is the macro, where would i enter the loop you describe?

    THANKS FOR YOUR HELP!!!

    Sub Sort_MoM_by_ColQ()
    '
    ' Sort_MoM_by_ColQ Macro
    '
    '
    Rows("7:17").Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
    Range("Q7:Q17"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
    :=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
    .SetRange Range("A7:CA17")
    .HEADER = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Rows("19:27").Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
    Range("Q19:Q27"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
    :=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
    .SetRange Range("A19:CA27")
    .HEADER = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Rows("29:38").Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
    Range("Q29:Q38"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
    :=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
    .SetRange Range("A29:CA38")
    .HEADER = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Rows("40:50").Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
    Range("Q40:Q50"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
    :=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
    .SetRange Range("A40:CA50")
    .HEADER = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 50
    Rows("52:62").Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
    Range("Q52:Q62"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
    :=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
    .SetRange Range("A52:CA62")
    .HEADER = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Rows("64:72").Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
    Range("Q64:Q72"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
    :=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
    .SetRange Range("A64:CA72")
    .HEADER = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Rows("74:82").Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
    Range("Q74:Q82"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
    :=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
    .SetRange Range("A74:CA82")
    .HEADER = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Rows("84:93").Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
    Range("Q84:Q93"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
    :=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
    .SetRange Range("A84:CA93")
    .HEADER = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Rows("95:101").Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
    Range("Q95:Q101"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
    :=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
    .SetRange Range("A95:CA101")
    .HEADER = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Rows("103:112").Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
    Range("Q103:Q112"), SortOn:=xlSortOnValues, Order:=xlDescending, _
    DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
    .SetRange Range("A103:CA112")
    .HEADER = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Rows("114:123").Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
    Range("Q114:Q123"), SortOn:=xlSortOnValues, Order:=xlDescending, _
    DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
    .SetRange Range("A114:CA123")
    .HEADER = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Rows("125:134").Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
    Range("Q125:Q134"), SortOn:=xlSortOnValues, Order:=xlDescending, _
    DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
    .SetRange Range("A125:CA134")
    .HEADER = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    End Sub

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Tom,

    I've tried to clean up the recorded macro and add the loop to do all the sheets in the current workbook. Of course I've done this in notepad and thus it is not tested so copy it into a COPY of one of your workbooks for testing there may be some minor glitches. I'm also sure it could use some more cleanup but w/o the workbook I can't do that.
    Code:
    Sub Sort_MoM_by_ColQ()
    '
    ' Sort_MoM_by_ColQ Macro
    '
    '
       Dim shtActive as Worksheet
    
    For Each shtActive in ActiveWorkbook.Worksheets
       With shtActive
           .Select
           Rows("7:17").Select
           .Sort.SortFields.Clear
           .Sort.SortFields.Add Key:= _
             Range("Q7:Q17"), SortOn:=xlSortOnValues, Order:=xlDescending, _
             DataOption:=xlSortNormal
    
           With .Sort
                .SetRange Range("A7:CA17")
                .HEADER = xlGuess
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
           End With     'With .Sort
    
           Rows("19:27").Select
           .Sort.SortFields.Clear
           .Sort.SortFields.Add Key:= _
               Range("Q19:Q27"), SortOn:=xlSortOnValues, Order:=xlDescending, _
               DataOption:=xlSortNormal
    
           With .Sort
               .SetRange Range("A19:CA27")
               .HEADER = xlGuess
               .MatchCase = False
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
           End With    '.Sort
    
           Rows("29:38").Select
           .Sort.SortFields.Clear
           .Sort.SortFields.Add Key:= _
               Range("Q29:Q38"), SortOn:=xlSortOnValues, Order:=xlDescending, _
               DataOption:=xlSortNormal
    
           With .Sort
               .SetRange Range("A29:CA38")
               .HEADER = xlGuess
               .MatchCase = False
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
           End With     '.Sort
    
           Rows("40:50").Select
           .Sort.SortFields.Clear
           .Sort.SortFields.Add Key:= _
               Range("Q40:Q50"), SortOn:=xlSortOnValues, Order:=xlDescending, _
               DataOption:=xlSortNormal
    
           With .Sort
               .SetRange Range("A40:CA50")
               .HEADER = xlGuess
               .MatchCase = False
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
           End With     '.Sort
    
           Rows("52:62").Select
           .Sort.SortFields.Clear
           .Sort.SortFields.Add Key:= _
              Range("Q52:Q62"), SortOn:=xlSortOnValues, Order:=xlDescending,_
              DataOption:=xlSortNormal
    
           With .Sort
               .SetRange Range("A52:CA62")
               .HEADER = xlGuess
               .MatchCase = False
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
           End With      '.Sort
    
           Rows("64:72").Select
           .Sort.SortFields.Clear
           .Sort.SortFields.Add Key:= _
             Range("Q64:Q72"), SortOn:=xlSortOnValues, Order:=xlDescending, -
             DataOption:=xlSortNormal
    
           With .Sort
               .SetRange Range("A64:CA72")
               .HEADER = xlGuess
               .MatchCase = False
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
           End With     '.Sort
    
           Rows("74:82").Select
           .Sort.SortFields.Clear
           .Sort.SortFields.Add Key:= _
              Range("Q74:Q82"), SortOn:=xlSortOnValues, Order:=xlDescending, _
              DataOption:=xlSortNormal
    
           With .Sort 
               .SetRange Range("A74:CA82")
               .HEADER = xlGuess
               .MatchCase = False
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
           End With     '.Sort
    
           Rows("84:93").Select
           .Sort.SortFields.Clear
           .Sort.SortFields.Add Key:= _
              Range("Q84:Q93"), SortOn:=xlSortOnValues, _
              DataOption:=xlSortNormal
    
           With .Sort
               .SetRange Range("A84:CA93")
               .HEADER = xlGuess
               .MatchCase = False
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
           End With      '.Sort
    
           Rows("95:101").Select
           .Sort.SortFields.Clear
           .Sort.SortFields.Add Key:= _
              Range("Q95:Q101"), SortOn:=xlSortOnValues, Order:=xlDescending, _
              DataOption:=xlSortNormal
    
           With .Sort
               .SetRange Range("A95:CA101")
               .HEADER = xlGuess
               .MatchCase = False
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
           End With      '.Sort
    
           Rows("103:112").Select
           .Sort.SortFields.Clear
           .Sort.SortFields.Add Key:= _
              Range("Q103:Q112"), SortOn:=xlSortOnValues, Order:=xlDescending, _
              DataOption:=xlSortNormal
    
           With .Sort
               .SetRange Range("A103:CA112")
               .HEADER = xlGuess
               .MatchCase = False
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
           End With       '.Sort
    
           Rows("114:123").Select
           .Sort.SortFields.Clear
           .Sort.SortFields.Add Key:= _
              Range("Q114:Q123"), SortOn:=xlSortOnValues, Order:=xlDescending, _
              DataOption:=xlSortNormal
    
           With .Sort
               .SetRange Range("A114:CA123")
               .HEADER = xlGuess
               .MatchCase = False
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
           End With      '.Sort
    
           Rows("125:134").Select
           .Sort.SortFields.Clear
           .Sort.SortFields.Add Key:= _
              Range("Q125:Q134"), SortOn:=xlSortOnValues, Order:=xlDescending, _
              
    
           With .Sort
               .SetRange Range("A125:CA134")
               .HEADER = xlGuess
               .MatchCase = False
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
          End With     '.Sort
    
       End With    'shtActive
    
    Next shtActive     'End for Each Loop
    
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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