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.



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

 

[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 ;~)