Date order on multiple sheets

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,021
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'

Code:
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

Well-known Member
Joined
Jun 29, 2014
Messages
859
Hello Patriot,

Try fully qualifying the code as follows:-


Code:
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:-


Code:
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.
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
859
You're welcome Patriot. I'm glad to have been able to assist.

Cheerio,
vcoolio.
 

Forum statistics

Threads
1,085,888
Messages
5,386,572
Members
402,006
Latest member
stringer47

Some videos you may like

This Week's Hot Topics

Top