1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2010 - Sort multiple fields in multiple sheets

Discussion in 'Business Applications' started by acejackhammer, Mar 17, 2013.

Thread Status:
Not open for further replies.
  1. acejackhammer

    acejackhammer Thread Starter

    Joined:
    Apr 14, 2011
    Messages:
    70
    Hello VBA experts. Need your help. I have a workbook with 14 worksheets. Each worksheet has several fields all named the same. I need to sort 3 fields in each worksheet. Those are ascending in this order; Patient ID (in column A), DOS (in column E) and Code (column B).


    Does anyone know of a code that can easily sort all 14 tabs at once? The range of data in each worksheet are different. Some worksheets have very little data to sort while other worksheets have maybe a 2,000 rows of data.


    Any help is greatly appreciated.
     
  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi

    Here is a macro that will sort all spreadsheets each with 3 levels of sort, all ascending assuming that the number of rows in all columns is the same.

    Give this a try on a copy of your file (can't stress this enough) and let us know what it didn't do right

    Code:
    [SIZE=1]Sub SortSheets()[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]'[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]' Macro1 Macro[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]'[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]For i = 1 To Sheets.Count[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]    ActiveWorkbook.Worksheets(i).Sort.SortFields.Clear[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]    ActiveWorkbook.Worksheets(i).Sort.SortFields.Add Key:=Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row) _[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]    ActiveWorkbook.Worksheets(i).Sort.SortFields.Add Key:=Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row) _[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]    ActiveWorkbook.Worksheets(i).Sort.SortFields.Add Key:=Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row) _[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]    With ActiveWorkbook.Worksheets(i).Sort[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]        .SetRange Range("A1:E" & Range("A" & Rows.Count).End(xlUp).Row)[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]        .Header = xlYes[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]        .MatchCase = False[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]        .Orientation = xlTopToBottom[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]        .SortMethod = xlPinYin[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]        .Apply[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]    End With[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]Next[/SIZE]
    [SIZE=1][/SIZE][SIZE=1]End Sub[/SIZE]
    
     
  3. acejackhammer

    acejackhammer Thread Starter

    Joined:
    Apr 14, 2011
    Messages:
    70
    Cant thank you enough. Really great. Awesome XCubed!
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1093425

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice