How to Auto Sort Multiple Sheets Using VBA in Microsoft Excel 2010

In this article, you will learn how to automatically sort multiple sheets using VBA code.

Q) I would like to know how I can sort the data on multiple worksheets (not all of them).

Let us take an example:

We have 3 sheets (Jan, Feb & Mar) & out of them only Mar sheet needs to be auto sorted as you enter any information in column A.
 
Click on Developer tab

From Code group, select Visual Basic
 
img1
 
Enter the following code in this Workbook (not in any sheet). This will run the code in all the worksheets.

Private Sub Workbook_SheetChange(ByValSh As Object, ByVal Target As Range)

 On Error Resume Next

    Select Case ActiveSheet.Name

       Case "Jan", "Feb"

       Case Else

    If Not Intersect(Target, Range("A:A")) Is Nothing Then

Range("A1").Sort Key1:=Range("A2"), _

          Order1:=xlAscending, Header:=xlYes, _

OrderCustom:=1, MatchCase:=False, _

          Orientation:=xlTopToBottom

    End If

   End Select

End Sub
 
img2
 
In this way, you can easily sort the data by any specific column in multiple sheets as you type the information. This is a great way to save time & ensuring the report looks the way you would like to see.

Comments

  1. Looks great, but it will slow down the excel drastically. If we create a button or may be create a separate field in tailored menu, that will keep the operational speed intact.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.