- To post as a guest, your comment is unpublished.· 2 months agoHello, Lucy,
To sort all sheets exclude the header row, please apply the below vba code:(Note: please change the cell references to your need)
Dim WS As Worksheet
Dim xIntR As Integer
On Error Resume Next
Application.ScreenUpdating = False
For Each WS In Worksheets
xIntR = Intersect(WS.UsedRange, WS.Range("A:F")).Rows.Count
WS.Range("A2:F" & xIntR).Sort Key1:=WS.Range("A2:A" & xIntR), Order1:=xlDescending
Application.ScreenUpdating = True
Please try, hope it can help you!
- To post as a guest, your comment is unpublished.· 29 days agoIn the hope that you see this...! This works wonderfully - but it includes cells with a formula but no data, creating gaps in the tabs and messing up the data on the rows. I need to keep those cells in my tabs as they look up names added to the 'master tab' I want it to only sort cells with an actual name - a quick fix???
How to sort data on multiple worksheets at once?
In Excel, we can sort data in a worksheet based on a specific column quickly and easily, but, have you ever tried to sort the data across multiple worksheet? To sort them one by one will be time-consuming, this article, I will introduce an easy way for solving it.
To sort data based on one column in all sheets of your workbook, the following VBA code can help you.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Sort data on multiple worksheets at once:
Sub SortAllSheets() 'Updateby Extendoffice Dim WS As Worksheet ActiveSheet.Range("a1:f1").Select Selection.Copy On Error Resume Next Application.ScreenUpdating = False For Each WS In Worksheets WS.Columns("A:F").Sort Key1:=WS.Columns("E"), Order1:=xlDescending Next WS ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll Application.ScreenUpdating = True End Sub
3. Then press F5 key to run this code, all sheets which have the same formatting have been sorted based on column E of each sheet in descending order at once.
Note: In the above code, A:F is the data range that you want to sort, E is the column letter which you want to sort based on.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 4 months agoworks well but how can I avoid including the headings (ie row 1?)
- To post as a guest, your comment is unpublished.· 6 months agoworked like a charm for me...thank you!
- To post as a guest, your comment is unpublished.· 1 years agoDoesn't work...