How to exclude the first row from sorting(vba)

Question

I need to sort multiple worksheets at once using this script

Sub SortAllSheets()
   Dim WS      As Worksheet
   ActiveSheet.Range("a2:f2").Select
   Selection.Copy
   On Error Resume Next
   Application.ScreenUpdating = False
   For Each WS In Worksheets
      WS.Columns("A:F").Sort Key1:=WS.Columns("D"), Order1:=xlAscending
   Next WS
   ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll
   Application.ScreenUpdating = True
End Sub

Is there a way to how to define that the first row(header)in every worksheet will be excluded from the sorting? I tried to modify the data input or add some extra function, but with no success. Thanks for any advices.

Add header:=xlYes to the sort.

Scott Craner
– Scott Craner

2018-11-29 22:25:16 +00:00
Commented Nov 29, 2018 at 22:25 — Scott Craner
– Scott Craner, Commented Nov 29, 2018 at 22:25

DeerSpotter · Accepted Answer · 2018-11-29 22:58:22Z

2

an example:

Dim strDataRange As Range
Dim keyRange As Range
Set strDataRange = Range("Your Data Range")
Set keyRange = Range("Your Sort by Column")
strDataRange.Sort Key1:=keyRange, Header:=xlYes

so using your code:

Sub SortAllSheets()
   Dim WS      As Worksheet
   ActiveSheet.Range("a2:f2").Select
   Selection.Copy
   On Error Resume Next
   Application.ScreenUpdating = False
   For Each WS In Worksheets
      WS.Columns("A:F").Sort Key1:=WS.Columns("D"), Order1:=xlAscending, Header:=xlYes
   Next WS
   ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll
   Application.ScreenUpdating = True
End Sub

answered Nov 29, 2018 at 22:58

DeerSpotter

4311 gold badge6 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to exclude the first row from sorting(vba)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related