Date order on multiple sheets

[Patriot2879]

Hi Please help, I have the code below where I have multiple sheets rows from A:Q and the source is B which is the date what I want from oldest to latest, B1 is the header so it should start from B2.

I am gettng an error on the 'Next'

Private Sub CommandButton2_Click()
Dim wksht As Worksheet
    For Each wksht In ThisWorkbook.Worksheets
        wksht.Select

With Range("A2:Q" & Cells(Rows.Count, "A").End(xlUp).Row)
.Sort Key1:=Range("B2"), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
End With
  Next
End Sub

 

[vcoolio]

Hello Patriot,

Try fully qualifying the code as follows:-

Private Sub CommandButton2_Click()

        Dim wksht As Worksheet
        
For Each wksht In ThisWorkbook.Worksheets
        

With wksht.Range("A2:Q" & wksht.Cells(Rows.Count, "A").End(xlUp).Row)
        .Sort Key1:=wksht.Range("B2"), _
        Order1:=xlAscending, _
        Header:=xlNo, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
End With

Next wksht
  
End Sub

........or you could try this abbreviated version:-

Private Sub CommandButton2_Click()

    Dim ws As Worksheet

For Each ws In Worksheets
    ws.Range("A2", ws.Range("Q" & ws.Rows.Count).End(xlUp)).Sort ws.[B2], 1
Next ws

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[Patriot2879]

Great thank you for your help

 

[vcoolio]

You're welcome Patriot. I'm glad to have been able to assist.

Cheerio,
vcoolio.