Hello,
I've recorded a Macro that filters a column by color, and then sorts another column in ascending order.
I'm trying to make this code more dynamic so I can reuse it elsewhere.
I've been playing around with ways to find the active Sheet (ActiveSheet.Name )and active Table (ActiveCell.ListObject.Name) first, and then find the Column Number based on the Column Heading but I can't make sense of the plethora of examples I've seen online.
Ultimately, I'd like to:
Sample Data
Note: I created this sample data using only two columns...
I've recorded a Macro that filters a column by color, and then sorts another column in ascending order.
I'm trying to make this code more dynamic so I can reuse it elsewhere.
I've been playing around with ways to find the active Sheet (ActiveSheet.Name )and active Table (ActiveCell.ListObject.Name) first, and then find the Column Number based on the Column Heading but I can't make sense of the plethora of examples I've seen online.
Ultimately, I'd like to:
- Replace "tblBooks" with Active Table
- Replace "Books" with Active Sheet
- Replace the column number 15 in (Field:=15) with an integer variable that's determined after finding a specified Header value first such as "Book Status" in the case of the following Macro.
- Possibly a variable for the RGB or HEX color values
VBA Code:
Sub TestAutoFilter()
'Filter Book Status by color
ActiveSheet.ListObjects("tblBooks").Range.AutoFilter Field:=15, _
Criteria1:=RGB(217, 225, 242), Operator:=xlFilterCellColor
'Sort Test Date ascending
ActiveWorkbook.Worksheets("Books").ListObjects("tblBooks").Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Books").ListObjects("tblBooks").Sort. _
SortFields.Add2 Key:=Range("tblBooks[[#Headers],[#Data],[Test Date]]"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Books").ListObjects("tblBooks").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Sample Data
Note: I created this sample data using only two columns...
Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | Test Date | Book Status | ||
2 | 10-Feb | Active | ||
3 | 10-Feb | Held | ||
4 | 10-Feb | In Transit | ||
5 | 09-Feb | Active | ||
6 | 09-Feb | Held | ||
7 | 09-Feb | In Transit | ||
8 | 08-Feb | In Transit | ||
9 | 07-Feb | Active | ||
10 | 07-Feb | Held | ||
11 | 07-Feb | In Transit | ||
12 | 06-Feb | Active | ||
13 | 06-Feb | Held | ||
14 | 03-Feb | Active | ||
15 | 03-Feb | In Transit | ||
Books |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
B2:B15 | Expression | =SUM(COUNTIF($B2,cfBookStatus)) | text | NO |