Need help updating this VBA macro to work in a different way. Sorting

[zone709]

Hi right now I use this to sort by trade order as you can see in the code for column C. It sorts it in the order I have below, but not I need to sort it also by employee name in alphabetic order. for Column B. I probably need to change something small here but not sure.

Sub Sorting120()
Dim rng1 As String
Dim rng2 As String
Dim keyRange As Variant
Dim sortNum As Long
keyRange = Array("Office", "Field Intern", "Trainer", "Supervisor", "Assistant", "Safety", "Super", "Super - Local 15D", "Assistant Field Engineer", "Operator - Local 14", "Pump Operator - Local 14", "Hoist-Local 14", "Crane Operator - Local 14", "Operator - Local 15", "Welder - Local 15", "Local (15D)", "Maintenance Engineer - Local 15", _
"Crane Oiler Operator - Local 15", "Pump Operator - Local 15", "Surveyor - Local 15D", "Shop Steward - Local 20", "Concrete Laborer - Local 6, 18A, 20", "Concrete Laborer-B Rate Apprentice", "Concrete Laborer Foreman - Local 6, 18A, 20", "Concrete Laborer-B Rate", "Concrete Laborer-A Rate Apprentice- 50%", "Concrete Laborer-B Rate Apprentice-50%", _
"Concrete Laborer-A Rate Apprentice- 80%", "Carpenter Super", "Carpenter - Local 20", "Carpenter Foreman - Local 20", "Carpenter Apprentice - Local 20", "Carpenter - Local 157", "Carpenter Foreman - Local 157", "Carpenter General Foreman - Local 157", "Carpenter Provisional", "Carpenter - Provisional", "Carpenter Utility", "Dockbuilder - Local 1556", _
"Dockbuilder - Local 1456", "Dockbuilder Foreman - Local 1456", "Dockbuilder Foreman - Local 1556", "Dockbuilder - Local 1456 - Apprentice", "Dockbuilder - Local 1556 2nd Year App", "Excavation Laborer - Local 731", "Excavation Laborer - Local 731 - Foreman", "Driller - Local 29", "Teamster Foreman", "Yard")
Application.AddCustomList ListArray:=keyRange
sortNum = Application.CustomListCount
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("C4:C" & CStr(Cells.Find("MASONS1", LookIn:=xlValues, LookAt:=xlWhole).Row - 1)), _
    SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=sortNum, DataOption:=xlSortNormal
    
With ActiveWorkbook.Worksheets("Data").Sort
    .SetRange Range("B4:AH" & Cells.Find("MASONS1", LookIn:=xlValues, LookAt:=xlWhole).Row - 1)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

This is a small example what I get now right which is good.

Excel 2016 (Windows) 32 bit

	A​	B​	C​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​	R​	S​	T​	U​	V​	W​	X​	Y​	Z​	AA​	AB​	AC​	AD​	AE​	AF​	AG​	AH​
1​	SNO​	Employee Name​	Local\Trade​	MONDAY​				TUESDAY​				WEDNESDAY​				THURSDAY​				FRIDAY​				SATURDAY​			SUNDAY​			TOTAL​
2​				7/3/2017​				7/4/2017​				7/5/2017​				7/6/2017​				7/7/2017​				7/8/2017​			7/9/2017​
3​				ST​	OT​	DT​	JOB​	ST​	OT​	DT​	JOB​	ST​	OT​	DT​	JOB​	ST​	OT​	DT​	JOB​	ST​	OT​	DT​	JOB​	OT​	DT​	JOB​	OT​	DT​	JOB​	ST​	OT​	DT​	Notes​
4​	1​	John	Office	8.00​				8.00​				8.00​								8.00​										32.00​	0.00​	0.00​
5​	2​	Frank	Office	8.00​				8.00​				8.00​								8.00​										32.00​	0.00​	0.00​
6​	3​	maria	Office	8.00​				8.00​				8.00​								8.00​										32.00​	0.00​	0.00​
7​	4​	Nick	Safety	V				8.00​				V																		8.00​	0.00​	0.00​
8​	5​	Stacy	Super	8.00​				8.00​				8.00​																		24.00​	0.00​	0.00​
9​	6​	karl	Super	8.00​				8.00​																						16.00​	0.00​	0.00​
10​	7​	donna	Super	8.00​				8.00​																						16.00​	0.00​	0.00​
11​	8​	mary	Assistant Field Engineer					H				8.00​																		8.00​	0.00​	0.00​
12​	9​	leo	Assistant Field Engineer	8.00​				8.00​																						16.00​	0.00​	0.00​
13​	10​	nicky	Operator - Local 14	8.00​	1.00​			8.00​				8.00​	1.00​				1.00​													24.00​	3.00​	0.00​
14​	11​	sal	Operator - Local 14	8.00​				8.00​																						16.00​	0.00​	0.00​
15​	12​	Vincent	Operator - Local 14	8.00​		1.00​		8.00​																						16.00​	0.00​	1.00​
16​	13​	newt	Operator - Local 14					8.00​																						8.00​	0.00​	0.00​
17​	14​	bill	Operator - Local 14	8.00​				8.00​																						16.00​	0.00​	0.00​
18​	15​	eddie	Operator - Local 14	8.00​				8.00​				8.00​																		24.00​	0.00​	0.00​
19​	16​	Brandon	Pump Operator - Local 14					8.00​				8.00​	2.00​																	16.00​	2.00​	0.00​
20​	17​	look	Crane Operator - Local 14	8.00​				8.00​				8.00​		3.00​				1.50​												24.00​	0.00​	4.50​
21​	18​	carmine	Crane Operator - Local 14	8.00​				8.00​				8.00​	1.50​																	24.00​	1.50​	0.00​
22​	19​	lipse	Operator - Local 15	9D				8.00​				8.00​	2.00​				1.00​													16.00​	3.00​	0.00​
23​	20​	Alberto	Operator - Local 15					8.00​				8.00​	1.00​																	16.00​	1.00​	0.00​
24​	21​	Abel	Operator - Local 15	8.00​				8.00​																						16.00​	0.00​	0.00​
25​	22​	Chris	Operator - Local 15	8.00​				8.00​				8.00​	2.00​																	24.00​	2.00​	0.00​
26​	23​	Tom	Operator - Local 15	8.00​				Bulk				Bulk																		8.00​	0.00​	0.00​
27​	24​	sal	Welder - Local 15	8.00​				8.00​																						16.00​	0.00​	0.00​
28​	25​	Frank	Welder - Local 15	8.00​				8.00​																						16.00​	0.00​	0.00​
29​	26​	George	Crane Oiler Operator - Local 15	8.00​				8.00​				8.00​		3.00​				1.50​												24.00​	0.00​	4.50​
30​	27​	vin	Surveyor - Local 15D	8.00​				8.00​				9D																		16.00​	0.00​	0.00​
31​	43​	kim	Surveyor - Local 15D					8.00​																						8.00​	0.00​	0.00​
32​	44​	gia	Surveyor - Local 15D	8.00​																										8.00​	0.00​	0.00​
33​	45​	Jayden	Surveyor - Local 15D	bulk				bulk				8.00​																		8.00​	0.00​	0.00​

<tbody>
</tbody>

Sheet: Data

<tbody>
</tbody>

But this is what I'm looking to do below. You can see the employee's name are in alphabetic order by the trade which that stays the same.

So basically after the trade goes into the order as you see I need column B to also sort in alpha order to each trade I have in the code.

Excel 2016 (Windows) 32 bit

	A​	B​	C​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​	R​	S​	T​	U​	V​	W​	X​	Y​	Z​	AA​	AB​	AC​	AD​	AE​	AF​	AG​	AH​
1​	SNO​	Employee Name​	Local\Trade​	MONDAY​				TUESDAY​				WEDNESDAY​				THURSDAY​				FRIDAY​				SATURDAY​			SUNDAY​			TOTAL​
2​				7/3/2017​				7/4/2017​				7/5/2017​				7/6/2017​				7/7/2017​				7/8/2017​			7/9/2017​
3​				ST​	OT​	DT​	JOB​	ST​	OT​	DT​	JOB​	ST​	OT​	DT​	JOB​	ST​	OT​	DT​	JOB​	ST​	OT​	DT​	JOB​	OT​	DT​	JOB​	OT​	DT​	JOB​	ST​	OT​	DT​	Notes​
4​	2​	Frank	Office	8.00​				8.00​				8.00​								8.00​										32.00​	0.00​	0.00​
5​	1​	John	Office	8.00​				8.00​				8.00​								8.00​										32.00​	0.00​	0.00​
6​	3​	maria	Office	8.00​				8.00​				8.00​								8.00​										32.00​	0.00​	0.00​
7​	4​	Nick	Safety	V				8.00​				V																		8.00​	0.00​	0.00​
8​	7​	donna	Super	8.00​				8.00​																						16.00​	0.00​	0.00​
9​	6​	karl	Super	8.00​				8.00​																						16.00​	0.00​	0.00​
10​	5​	Stacy	Super	8.00​				8.00​				8.00​																		24.00​	0.00​	0.00​
11​	9​	leo	Assistant Field Engineer	8.00​				8.00​																						16.00​	0.00​	0.00​
12​	8​	mary	Assistant Field Engineer					H				8.00​																		8.00​	0.00​	0.00​
13​	14​	bill	Operator - Local 14	8.00​				8.00​																						16.00​	0.00​	0.00​
14​	15​	eddie	Operator - Local 14	8.00​				8.00​				8.00​																		24.00​	0.00​	0.00​
15​	13​	newt	Operator - Local 14					8.00​																						8.00​	0.00​	0.00​
16​	10​	nicky	Operator - Local 14	8.00​	1.00​			8.00​				8.00​	1.00​				1.00​													24.00​	3.00​	0.00​
17​	11​	sal	Operator - Local 14	8.00​				8.00​																						16.00​	0.00​	0.00​
18​	12​	Vincent	Operator - Local 14	8.00​		1.00​		8.00​																						16.00​	0.00​	1.00​
19​	16​	Brandon	Pump Operator - Local 14					8.00​				8.00​	2.00​																	16.00​	2.00​	0.00​
20​	18​	carmine	Crane Operator - Local 14	8.00​				8.00​				8.00​	1.50​																	24.00​	1.50​	0.00​
21​	17​	look	Crane Operator - Local 14	8.00​				8.00​				8.00​		3.00​				1.50​												24.00​	0.00​	4.50​
22​	21​	Abel	Operator - Local 15	8.00​				8.00​																						16.00​	0.00​	0.00​
23​	20​	Alberto	Operator - Local 15					8.00​				8.00​	1.00​																	16.00​	1.00​	0.00​
24​	22​	Chris	Operator - Local 15	8.00​				8.00​				8.00​	2.00​																	24.00​	2.00​	0.00​
25​	19​	lipse	Operator - Local 15	9D				8.00​				8.00​	2.00​				1.00​													16.00​	3.00​	0.00​
26​	23​	Tom	Operator - Local 15	8.00​				Bulk				Bulk																		8.00​	0.00​	0.00​
27​	25​	Frank	Welder - Local 15	8.00​				8.00​																						16.00​	0.00​	0.00​
28​	24​	sal	Welder - Local 15	8.00​				8.00​																						16.00​	0.00​	0.00​
29​	26​	George	Crane Oiler Operator - Local 15	8.00​				8.00​				8.00​		3.00​				1.50​												24.00​	0.00​	4.50​
30​	44​	gia	Surveyor - Local 15D	8.00​																										8.00​	0.00​	0.00​
31​	45​	Jayden	Surveyor - Local 15D	bulk				bulk				8.00​																		8.00​	0.00​	0.00​

<tbody>
</tbody>

Sheet: Data

<tbody>
</tbody>

 

[zone709]

I figured it out and got the results I needed. The new but similar code on the bottom works but in this line below. I have to put the range as ("B4:B250") instead of "B4:B". if I put this it breaks. I can not figure out why.

ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("B4:B250"), _<strike></strike>

Sub Sorting122()
Dim rng1 As String
Dim rng2 As String
Dim keyRange As Variant
Dim sortNum As Long
keyRange = Array("Office", "Field Intern", "Trainer", "Supervisor", "Assistant", "Safety", "Super", "Super - Local 15D", "Assistant Field Engineer", "Operator - Local 14", "Pump Operator - Local 14", "Hoist-Local 14", "Crane Operator - Local 14", "Operator - Local 15", "Welder - Local 15", "Local (15D)", "Maintenance Engineer - Local 15", _
"Crane Oiler Operator - Local 15", "Pump Operator - Local 15", "Surveyor - Local 15D", "Shop Steward - Local 20", "Concrete Laborer - Local 6, 18A, 20", "Concrete Laborer-B Rate Apprentice", "Concrete Laborer Foreman - Local 6, 18A, 20", "Concrete Laborer-B Rate", "Concrete Laborer-A Rate Apprentice- 50%", "Concrete Laborer-B Rate Apprentice-50%", _
"Concrete Laborer-A Rate Apprentice- 80%", "Carpenter Super", "Carpenter - Local 20", "Carpenter Foreman - Local 20", "Carpenter Apprentice - Local 20", "Carpenter - Local 157", "Carpenter Foreman - Local 157", "Carpenter General Foreman - Local 157", "Carpenter Provisional", "Carpenter - Provisional", "Carpenter Utility", "Dockbuilder - Local 1556", _
"Dockbuilder - Local 1456", "Dockbuilder Foreman - Local 1456", "Dockbuilder Foreman - Local 1556", "Dockbuilder - Local 1456 - Apprentice", "Dockbuilder - Local 1556 2nd Year App", "Excavation Laborer - Local 731", "Excavation Laborer - Local 731 - Foreman", "Driller - Local 29", "Teamster Foreman", "Yard")
Application.AddCustomList ListArray:=keyRange
sortNum = Application.CustomListCount
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("C4:C" & CStr(Cells.Find("MASONS", LookIn:=xlValues, LookAt:=xlWhole).Row - 1)), _
    SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=sortNum, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("B4:B250"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
    .SetRange Range("B4:AH" & Cells.Find("MASONS", LookIn:=xlValues, LookAt:=xlWhole).Row - 1)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

<strike></strike>