Question

To assist the sales staff, create a macro on the Pending Orders Status sheet for each of the four salespersons (Markia,

Akima, Steve and Juan) that does the following:


• Filters the list for their name in the Salesperson column


• Filter to shows Yes in the All Items in Stock column


• Sorts the results by County in descending order

Answer 1

To assist the sales staff, a macro can be created on the Pending Orders Status sheet for each salesperson to filter, show specific data, and sort the results in descending order.

Step-by-step explanation:

To create a macro on the Pending Orders Status sheet for each of the four salespersons, you can use Microsoft Excel's Visual Basic for Applications (VBA). The macro should consist of the following steps:

Filter the list for the specific salesperson's name in the Salesperson column.

Filter to show only the records with "Yes" in the All Items in Stock column.

Sort the filtered results by County in descending order.

Here is an example of VBA code that achieve this:

Sub FilterAndSort()
Dim ws As Worksheet
Set ws = Sheets("Pending Orders Status")
ws.Select
ws.Range("A1").AutoFilter Field:=2, Criteria1:="Markia"
ws.Range("A1").AutoFilter Field:=3, Criteria1:="Yes"
ws.Sort.SortFields.Add Key:=ws.Range("D2:D" & ws.Cells(Rows.Count, "D").End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortTextAsNumbers
With ws.Sort
.SetRange ws.UsedRange
.Header = xlYes
.Apply
End With
End Sub

This VBA code assumes that the Pending Orders Status sheet is in the same Excel workbook and that the salesperson's name is in the second column, the All Items in Stock column is in the third column, and the County column is in the fourth column.