- 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.· 20 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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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 20161216 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.· 11 months agoDoesn't work...