Results 1 to 5 of 5

Thread: Macro format

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

    Macro format

    I have a multi-tabbed workbook each tab is named differently, but contains a similar template, when i use the macro recorder to sort the first tab by column Q, the macro will not work to sort Tab#2 by column Q.
    Here is an example... what do i need to do to make the macro work on the other tabs?
    Code:
        Rows("7:17").Select
        ActiveWorkbook.Worksheets("69520").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("69520").Sort.SortFields.Add Key:=Range("Q7:Q17"), _
            SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("69520").Sort
            .SetRange Range("A7:AB17")
            .HEADER = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Rows("19:27").Select
        ActiveWorkbook.Worksheets("69520").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("69520").Sort.SortFields.Add Key:=Range("Q19:Q27") _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("69520").Sort
            .SetRange Range("A19:AB27")
            .HEADER = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Rows
    Last edited by RetiredGeek; 2013-04-14 at 20:43.

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

    If you change these references: ActiveWorkbook.Worksheets("69520")...
    to: ActiveWorkBook.ActiveSheet...

    It should work on all sheets. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following 2 Users Say Thank You to RetiredGeek For This Useful Post:

    DredWolf (2013-04-14),Unome (2013-04-14)

  4. #3
    New Lounger
    Join Date
    Apr 2013
    Location
    Clearwater,Canada
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Well, my VBA is not great, but you need the name of the sheet to change, so something along the lines of ActiveSheet may be needed here IE- everywhere you have "69520", replace with ActiveSheet...However, I suspect there is probably a much simpler solution, my VBA limitations definitely showing here

    Edit-
    Aah..see, knew I was on right track, just missed by a hair
    Last edited by DredWolf; 2013-04-14 at 20:42.

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

    If you want to sort all the worksheets in the workbook the same way:
    Code:
    Option Explicit
    
    Sub TestSort()
    
       Dim sht As Worksheet
       
       For Each sht In ActiveWorkbook.Worksheets
          Rows("1:9").Select
          With sht.Sort
              .SortFields.Clear
              .SortFields.Add Key:=Range("A1:A9"), _
                SortOn:=xlSortOnValues, _
                Order:=xlDescending, _
                DataOption:=xlSortNormal
              .SetRange Range("A1:AB9")
              .Header = xlGuess
              .MatchCase = False
              .Orientation = xlTopToBottom
              .SortMethod = xlPinYin
              .Apply
          End With
          
          [a1].Select
       
       Next sht
       
    End Sub
    Just change the ranges and options as appropriate to your situation
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thank you, this format worked for my specialized sort!!

Posting Permissions

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