Trying to do custom sort in VBA

[phyxius117]

Hey all,

After looking up guides, I tried to incorporate a line of code to mimic custom sort for my end product of this macro. Basically this macro takes existing tabs of active worksheet, copies them then perform some operations on them within the new worksheet. For some reason my sorting code isn't working. The Relevant Section is in blue and the sorting line is in red color. Can anyone tell me why and how to fix it? Thanks.

Sub Export()
Set wbThis = ThisWorkbook

Dim DCname As String
    DCname = ActiveWorkbook.Sheets("Reference").Range("R2").Value

Dim FiscalYear As String
    FiscalYear = ActiveWorkbook.Sheets("Reference").Range("R8").Value

Dim Period As String
    Period = ActiveWorkbook.Sheets("Reference").Range("R11").Value

Dim FileName As String
    If Worksheets("Reference").Range("R8") = "Test" Then
    FileName = InputBox("Enter File Name")
    Else
    FileName = DCname & "_SQL Data_" & FiscalYear & "_" & Period
    End If
    
Dim Visible As XlSheetVisibility
    Worksheets("Route Totals").Visible = xlSheetVisible
    Worksheets("Stops").Visible = xlSheetVisible
    Worksheets("Domicile Rates").Visible = xlSheetVisible
    
Worksheets(Array("Route Totals", "Stops", "Domicile Rates")).Copy

    With ActiveWorkbook.Worksheets("Route Totals")
    .UsedRange.Value = .UsedRange.Value
    .Cells.Columns.AutoFit
    End With
    
[COLOR=rgb(41, 105, 176)]    With ActiveWorkbook.Worksheets("Stops")
    .UsedRange.Value = .UsedRange.Value
    Dim lastrow As Long
    lastrow = .Range("M" & Rows.Count).End(xlUp).Row
    .Range("L3").Formula = "=miles($AL$2,AL3)"
    .Range("L3:L" & lastrow).FillDown
    .Range("L3:L" & lastrow).Copy
    .Range("L3:L" & lastrow).PasteSpecial Paste:=xlPasteValues
    .Range("AM3").Formula = "=IF(G3=0,0,tolls(AL2,AL3,true)*-1.05)"
    .Range("AM3:AM" & lastrow).FillDown
    .Range("AM3:AM" & lastrow).Copy
    .Range("AM3:AM" & lastrow).PasteSpecial Paste:=xlPasteValues
    .Range("AN2").Formula = "=IF(G2=0,sumif(B:B,B2,AM:AM),0)"
    .Range("AN2:AN" & lastrow).FillDown
    .Range("AN2:AN" & lastrow).Copy
    .Range("AN2:AN" & lastrow).PasteSpecial Paste:=xlPasteValues
    .Range("AO2").Formula = "=IFERROR(VLOOKUP(K2,'Domicile Rates'!A:AP,42,FALSE),0)"
    .Range("AO2:AO" & lastrow).FillDown
    .Range("AO2:AO" & lastrow).Copy
    .Range("AO2:AO" & lastrow).PasteSpecial Paste:=xlPasteValues
    .Cells.Columns.AutoFit
  [/COLOR][COLOR=rgb(226, 80, 65)]  Columns.Sort Key1:=Columns("C"), Order1:=xlAscending, Key2:=Columns("B"), Order2:=xlAscending, Key3:=Columns("G"), Order3:=xlAscending, Header:=xlYes[/COLOR]
[COLOR=rgb(41, 105, 176)]    End With[/COLOR]
    
    With ActiveWorkbook.Worksheets("Domicile Rates")
    .Visible = Visible
    End With
    
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & FileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

ThisWorkbook.Worksheets("Route Totals").Visible = Visible
ThisWorkbook.Worksheets("Stops").Visible = Visible
ThisWorkbook.Worksheets("Domicile Rates").Visible = Visible
End Sub

 

[phyxius117]

Sorry I'm kind of new to this forum. Not sure why the coloring of fonts didn't work.

Here is the relevant section of the code:

    With ActiveWorkbook.Worksheets("Stops")
    .UsedRange.Value = .UsedRange.Value
    Dim lastrow As Long
    lastrow = .Range("M" & Rows.Count).End(xlUp).Row
    .Range("L3").Formula = "=miles($AL$2,AL3)"
    .Range("L3:L" & lastrow).FillDown
    .Range("L3:L" & lastrow).Copy
    .Range("L3:L" & lastrow).PasteSpecial Paste:=xlPasteValues
    .Range("AM3").Formula = "=IF(G3=0,0,tolls(AL2,AL3,true)*-1.05)"
    .Range("AM3:AM" & lastrow).FillDown
    .Range("AM3:AM" & lastrow).Copy
    .Range("AM3:AM" & lastrow).PasteSpecial Paste:=xlPasteValues
    .Range("AN2").Formula = "=IF(G2=0,sumif(B:B,B2,AM:AM),0)"
    .Range("AN2:AN" & lastrow).FillDown
    .Range("AN2:AN" & lastrow).Copy
    .Range("AN2:AN" & lastrow).PasteSpecial Paste:=xlPasteValues
    .Range("AO2").Formula = "=IFERROR(VLOOKUP(K2,'Domicile Rates'!A:AP,42,FALSE),0)"
    .Range("AO2:AO" & lastrow).FillDown
    .Range("AO2:AO" & lastrow).Copy
    .Range("AO2:AO" & lastrow).PasteSpecial Paste:=xlPasteValues
    .Cells.Columns.AutoFit
    Columns.Sort Key1:=Columns("C"), Order1:=xlAscending, Key2:=Columns("B"), Order2:=xlAscending, Key3:=Columns("G"), Order3:=xlAscending, Header:=xlYes
    End With

 

[MARK858]

@phyxius117 if you want to use custom formatting in your code when posting you need to use the Rich code tags option rather than VBA code tags

Btw the first Columns in the line should be a range

Columns.Sort Key1:=Columns("C"), Order1:=xlAscending, Key2:=Columns("B"), Order2:=xlAscending, Key3:=Columns("G"), Order3:=xlAscending, Header:=xlYes

 

[phyxius117]

@phyxius117 if you want to use custom formatting in your code when posting you need to use the Rich code tags option rather than VBA code tags

Btw the first Columns in the line should be a range

Columns.Sort Key1:=Columns("C"), Order1:=xlAscending, Key2:=Columns("B"), Order2:=xlAscending, Key3:=Columns("G"), Order3:=xlAscending, Header:=xlYes

Hmm I modified and still it's not sorting...

Columns("A:BK").Sort Key1:=Columns("C"), Order1:=xlAscending, Key2:=Columns("B"), Order2:=xlAscending, Key3:=Columns("G"), Order3:=xlAscending, Header:=xlYes

 

[MARK858]

Sorts fine for me, with

Sub xxxx()
Range("A1:L21").Sort Key1:=Columns("C"), Order1:=xlAscending, Key2:=Columns("B"), Order2:=xlAscending, Key3:=Columns("G"), Order3:=xlAscending, Header:=xlYes
End Sub

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Order ID	Order Date	Site	SP Name	Category Name	Ship Via	Quantity	Order Total	Cust Surname	Birthdate	Gender
2	3	8	2	1	8	7	3	7	1	6	9	0.058064
3	6	5	3	6	1	5	9	7	6	1	10	0.14206
4	1	9	10	6	6	10	7	8	1	9	8	0.190358
5	3	4	4	9	6	7	6	4	7	10	9	0.205516
6	9	8	4	6	3	2	6	3	6	4	9	0.215084
7	7	4	6	3	6	4	3	6	1	1	8	0.257336
8	3	4	4	10	3	10	1	8	8	7	5	0.263102
9	8	4	4	6	1	9	10	3	9	6	9	0.318292
10	7	6	9	1	3	6	2	2	5	4	4	0.334219
11	8	3	4	9	3	5	9	8	6	6	3	0.404677
12	9	6	4	3	6	10	2	5	3	2	8	0.438228
13	7	9	4	6	7	7	10	5	6	1	3	0.475686
14	4	2	4	10	4	9	5	2	2	9	9	0.489278
15	8	8	10	7	9	6	8	3	2	7	1	0.516712
16	6	6	9	9	2	5	2	8	10	7	1	0.614971
17	3	4	9	9	5	1	4	9	4	9	5	0.691229
18	4	10	1	6	3	2	7	2	4	7	1	0.744385
19	5	9	8	10	10	2	6	9	4	1	1	0.75087
20	5	9	4	3	4	4	3	9	6	10	2	0.891264
21	7	1	4	8	5	1	10	5	9	8	10	0.896692
Sheet1

becomes the below (see the highlighted lines)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Order ID	Order Date	Site	SP Name	Category Name	Ship Via	Quantity	Order Total	Cust Surname	Birthdate	Gender
2	4	10	1	6	3	2	7	2	4	7	1	0.744385
3	3	8	2	1	8	7	3	7	1	6	9	0.058064
4	6	5	3	6	1	5	9	7	6	1	10	0.14206
5	7	1	4	8	5	1	10	5	9	8	10	0.896692
6	4	2	4	10	4	9	5	2	2	9	9	0.489278
7	8	3	4	9	3	5	9	8	6	6	3	0.404677
8	3	4	4	10	3	10	1	8	8	7	5	0.263102
9	3	4	4	9	6	7	6	4	7	10	9	0.205516
10	8	4	4	6	1	9	10	3	9	6	9	0.318292
11	9	6	4	3	6	10	2	5	3	2	8	0.438228
12	9	8	4	6	3	2	6	3	6	4	9	0.215084
13	5	9	4	3	4	4	3	9	6	10	2	0.891264
14	7	9	4	6	7	7	10	5	6	1	3	0.475686
15	7	4	6	3	6	4	3	6	1	1	8	0.257336
16	5	9	8	10	10	2	6	9	4	1	1	0.75087
17	3	4	9	9	5	1	4	9	4	9	5	0.691229
18	7	6	9	1	3	6	2	2	5	4	4	0.334219
19	6	6	9	9	2	5	2	8	10	7	1	0.614971
20	8	8	10	7	9	6	8	3	2	7	1	0.516712
21	1	9	10	6	6	10	7	8	1	9	8	0.190358
Sheet1

 

[MARK858]

Are you on the actual sheet being sorted as the way you have it written it will only work if Worksheets("Stops") is the activesheet

 

[Akuini]

Maybe you just missed to add some dots:

.Columns.Sort Key1:=.Columns("C"), Order1:=xlAscending, Key2:=.Columns("B"), Order2:=xlAscending, Key3:=.Columns("G"), Order3:=xlAscending, Header:=xlYes

 

[MARK858]

Maybe you just missed to add some dots:

.Columns.Sort Key1:=.Columns("C"), Order1:=xlAscending, Key2:=.Columns("B"), Order2:=xlAscending, Key3:=.Columns("G"), Order3:=xlAscending, Header:=xlYes

Probably