CODEWITHSUNDEEP
excelvba
donau
donau

Reputation: 23

How to exclude the first row from sorting(vba)

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.

Upvotes: 2

Views: 1438

Answers (1)

DeerSpotter
DeerSpotter

Reputation: 433

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

Upvotes: 2

Related Questions