Using Sort within a loop

[Dreamteam]

Hi guys,

I have written some code that looks at a database and filters on a user number and then pastes the data onto worksheets (named as the user numbers) - all of this part is working fine - I have created a very simple array to store the user numbers. I have some additional formatting for centering the data etc.

Option Explicit

Sub Macro1()


Dim wb As Workbook
Set wb = ThisWorkbook
Dim wsdata As Worksheet
Set wsdata = wb.Worksheets("data")
Dim rngdataforfilter As Range
Set rngdataforfilter = wsdata.Range("$A$1:$l$1")
Dim rngstartcell As Range
Set rngstartcell = wsdata.Range("a1")


Dim strdrivers(9) As String
Dim i As Integer


If wsdata.ProtectContents = True Then
  Exit Sub
End If




strdrivers(0) = "1"
strdrivers(1) = "3"
strdrivers(2) = "4"
strdrivers(3) = "5"
strdrivers(4) = "6"
strdrivers(5) = "7"
strdrivers(6) = "8"
strdrivers(7) = "14"
strdrivers(8) = "15"
strdrivers(9) = "17"


Application.ScreenUpdating = False


    For i = LBound(strdrivers) To UBound(strdrivers)
    
    rngdataforfilter.AutoFilter Field:=10, Criteria1:=strdrivers(i)
    rngstartcell.CurrentRegion.Copy
    
    Dim rngstartcelldriverws As Range
    Set rngstartcelldriverws = Worksheets(strdrivers(i)).Range("a1")
    
    With Worksheets(strdrivers(i))
       rngstartcelldriverws.PasteSpecial Paste:=xlPasteValues
      .Range("C:C").NumberFormat = "dd/mm/yyyy"
      .Columns.AutoFit
       rngstartcelldriverws.CurrentRegion.HorizontalAlignment = xlCenter
    End With
     
    Next i


Worksheets("data").ShowAllData
   
Application.ScreenUpdating = True
   
End Sub

However, the final piece to the puzzle is just to simply sort the data by date which is column C. I have tried many ways of doing this but none of them seem to work - which I am struggling to understand why not. I want to include this sort into my loop - where everything else is working fine. The annoying thing is that if I write the code procedurally - ie removing the loop and having the code many times larger than it is now - then it works?! I have not included any of the code that I have tried to use for the sorting.

I will try to include one of the worksheets that are created by the code so far.

Any help would be much appreciated.

MONTH	DAY	DATE	CONTRACT	PICK UP	DROP OFF	FARE (£)	DRIVER REF	PICK UP/RETURN
MAY	TUE	01/05/2018	COSAWES	COSAWES	PEN COLL	20	8	PICK UP
MAY	TUE	01/05/2018	COSAWES	PEN COLL	COSAWES	20	8	RETURN
MAY	WED	02/05/2018	B MEWS	MABE PRIM	B MEWS	15	8	RETURN
MAY	FRI	04/05/2018	BASS ACCACIA	BASS ACACIA	PEN COLL	12	8	PICK UP
MAY	FRI	04/05/2018	BASS ACCACIA	PEN COLL	BASS ACACIA	12	8	RETURN
MAY	WED	09/05/2018	SWAN MONGL	SWAN MONGL	FAL SCH	12	8	PICK UP
MAY	THU	10/05/2018	BASS ACCACIA	BASS ACACIA	PEN COLL	12	8	PICK UP
MAY	THU	10/05/2018	BASS ACCACIA	PEN COLL	BASS ACACIA	12	8	RETURN
MAY	MON	14/05/2018	TAP	PONSNOOTH	NEWQUAY	60	8	PICK UP
MAY	MON	14/05/2018	TAP	NEWQUAY	PONSANOOTH	60	8	RETURN
MAY	MON	14/05/2018	COSAWES	PEN COLL	COSAWES	20	8	RETURN
MAY	TUE	15/05/2018	BASS ACCACIA	BASS ACACIA	PEN COLL	12	8	PICK UP
MAY	TUE	15/05/2018	BASS ACCACIA	PEN COLL	BASS ACACIA	12	8	RETURN
MAY	TUE	15/05/2018	PENRYN COLLEGE/KERG	KERGILLIACK	PEN COLL	6	8	PICK UP
MAY	WED	16/05/2018	B MEWS	B MEWS	MABE PRIM	15	8	PICK UP
MAY	WED	16/05/2018	TREGEW	TREGEW	DRAC CNTRE	15	8	PICK UP
MAY	WED	16/05/2018	B MEWS	MABE PRIM	B MEWS	15	8	RETURN
MAY	THU	17/05/2018	COSAWES	COSAWES	PEN COLL	20	8	PICK UP
MAY	THU	17/05/2018	COSAWES	PEN COLL	COSAWES	20	8	RETURN
MAY	THU	03/05/2018	BAPTIST CHURCH	MEADOW,THEY	BAP CHURCH	4	8	PICK UP
MAY	THU	10/05/2018	BAPTIST CHURCH	MEADOW,THEY	BAP CHURCH	4	8	PICK UP
MAY	MON	21/05/2018	SWAN MONGL	SWAN MONGL	FAL SCH	12	8	PICK UP
MAY	MON	21/05/2018	SWAN MONGL	FAL SCH	SWANP MONGL	12	8	RETURN
MAY	TUE	22/05/2018	BERKLEY COTTAGES	LOWENA	FALCARE	25	8	RETURN

<tbody>
</tbody>

 

[Fluff]

How about

   With Worksheets(strdrivers(i))
      rngstartcelldriverws.PasteSpecial Paste:=xlPasteValues
      .Range("C:C").NumberFormat = "dd/mm/yyyy"
      .Columns.AutoFit
      rngstartcelldriverws.CurrentRegion.HorizontalAlignment = xlCenter
      .Sort.SortFields.Clear
      .Sort.SortFields.Add key:=Range("C2"), SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
      With .Sort
         .SetRange Range("A1").CurrentRegion
         .Header = xlYes
         .MatchCase = False
         .Orientation = xlTopToBottom
         .Apply
      End With
   End With

 

[Dreamteam]

Fluff - many, many thanks - this works perfectly. I will now go away and work out why my previous attempts failed as they were so close.

Once again - thank you :~)

 

[Fluff]

Glad to help & thanks for the feedback

 

[Dreamteam]

Glad to help & thanks for the feedback

Hi Fluff

Can I ask a question please about the solution that you gave me...

Are you actually using Sort as an object in its own right; it does look like it. I was under the impression that Sort was a method of the Range object and did not know that it could be used like this.

Many thanks

Dt

 

[shg]

It's a method of the range object in older versions of Excel, and it still works.

range("A1"),currentregion.sort key1:=range("C2"), Header:=xlyes

 

[Dreamteam]

It's a method of the range object in older versions of Excel, and it still works.

range("A1"),currentregion.sort key1:=range("C2"), Header:=xlyes

Hi shg

Thanks for this.

Can I dig a bit deeper here if you don’t mind - fairly new to vba but trying very hard to write the correct way ie trying to use references to ranges etc and NEVER using .Select ;~)

So, whilst it can be used as a method of Range object - it can also be used as a discrete object having its own methods and properties?

Many thanks

 

[shg]

See Help for Sort Object. Fluff's code shows examples of its properties and methods.

 

[Dreamteam]

Thanks Shg ;~)