Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Sorting by different columns automatically on two sheet tabs

    I'm looking for a way for two sheet tabs to contain the same information but be sorted by different columns. When data on the first tab is updated, I want the second tab to update automatically even though it is sorted by a different column. Any ideas?
    J. P. Zinn

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts

    Sort with VBA

    Both sheets will be sorted simultaneously. In this example, Sheet1 will be sorted by column A while Sheet2 will be sorted by column B. Change the columns and ranges to meet your preferences. Place in a standard module. This can be achieved simply by recording the macro as well.

    HTH,
    Maud
    SHEET1
    Sort1.jpg

    SHEET2
    Sort2.jpg


    Code:
    Public Sub SortSheets()
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:D3")
        .Header = xlNo
        .Apply
    End With
    
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:D3")
        .Header = xlNo
        .Apply
    End With
    End Sub
    Last edited by Maudibe; 2013-03-08 at 19:55.

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    jpzinn (2013-03-08)

  4. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Looks great. I guess that was too easy. J. P. Zinn

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    It's all relative. Thanks for the thanks
    Maud

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maudibe

    The original posted question isn't very clear.
    "When data on the first tab is updated, I want the second tab to update automatically.."

    I assumed this meant that if new data was placed on sheet1, then it would also be placed on sheet2.

    zeddy

  7. The Following User Says Thank You to zeddy For This Useful Post:

    Maudibe (2013-03-09)

  8. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts

    Slap my head

    Zeddy, thanks for picking up on that.

    JP, my apologies for misreading what you wanted to do. Below, find adjustments to the code to update the sheet with the addition of new data. You do not need to change the range, it will do so automatically but make sure the sheet names correspond to what you have named them.

    Code:
    Public Sub SortSheets()
    Application.ScreenUpdating = False
    Dim Usedrows1 As Range
    Dim Usedrows2 As Range
    
    'COPY SHEET1 WITH UPDATES TO SHEET2
    Worksheets("Sheet2").Cells.ClearContents
    Worksheets("Sheet1").Cells.Select
        Selection.Copy
        Sheets("Sheet2").Select
        Cells.Select
        ActiveSheet.Paste
    
    'SET RANGES FOR THE DATA ON EACH WORKSHEET
    Set Usedrows1 = Worksheets("sheet1").UsedRange
    Set Usedrows2 = Worksheets("sheet2").UsedRange
    
    'SORT BOTH SHEETS USING THE NEW RANGES
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Usedrows1
        .Header = xlNo
        .Apply
    End With
    
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Usedrows2
        .Header = xlNo
        .Apply
        Range("A1").Select
    End With
    
    'CLEANUP
    Worksheets("Sheet1").Activate
    Application.CutCopyMode = False
    Range("A1").Select
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-03-10 at 22:21. Reason: spelling corrections

Posting Permissions

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