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
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
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.
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.
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.