Excel VBA Autofilter field as a variable

[amazon_devil]

I am trying to develop a code that will autofilter a field, that will change month to month.

I have a userform where the user selects the reporting month, then the script finds that month across the top of a structured table, and then drops down 1 row to select the header of the structured table. upon clicking "OK" on the user form.

As the field number "column" will change each time I need to enter this as a variable. I have tried a number of different solutions from other peoples posts, but still no luck.

Private Sub cbOK_Click()

Sheets("Weekly Timesheet").Select
Sheets("Weekly Timesheet").Range("H5").Select
ActiveCell.value = cboRMonth.value
Unload Me


ReportMonth = cboRMonth.value
MsgBox ReportMonth


Sheets("Tracking (DAYS)").Select
Sheets("Tracking (DAYS)").Range("N2").Select


Do Until ActiveCell = ReportMonth
ActiveCell.Offset(0, 1).Select
Loop


ActiveCell.Offset(1, 0).Select


'Tells me what the name of the header is (just to make sure I've got the right one selected).
Dim Col As String
Col = ActiveCell
MsgBox Col


Dim lCol As Long
lCol = ActiveCell.Column


ActiveSheet.ListObjects("Tracking_DAYS").Range(lCol).AutoFilter _
Criterial:=">0", _
Operator:=x1FilterValues




End Sub

 

[Yongle]

Perhaps this will help
- note that selection of objects is not required- userform not used in my example but finding correct column can be done in exactly the same way
My table:

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td][/td][td][/td][td=bgcolor:#4472C4]Name[/td][td=bgcolor:#4472C4]01 2019[/td][td=bgcolor:#4472C4]02 2019[/td][td=bgcolor:#4472C4]03 2019[/td][td=bgcolor:#4472C4]04 2019[/td][td=bgcolor:#4472C4]05 2019[/td][td=bgcolor:#4472C4]06 2019[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td][/td][td=bgcolor:#D9E1F2]name01[/td][td=bgcolor:#D9E1F2]

10​

[/td][td=bgcolor:#D9E1F2]

7​

[/td][td=bgcolor:#D9E1F2]

30​

[/td][td=bgcolor:#D9E1F2]

-23​

[/td][td=bgcolor:#D9E1F2]

18​

[/td][td=bgcolor:#D9E1F2]

-5​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][td]name02[/td][td]

14​

[/td][td]

10​

[/td][td]

32​

[/td][td]

15​

[/td][td]

10​

[/td][td]

-12​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td=bgcolor:#D9E1F2]name03[/td][td=bgcolor:#D9E1F2]

19​

[/td][td=bgcolor:#D9E1F2]

14​

[/td][td=bgcolor:#D9E1F2]

28​

[/td][td=bgcolor:#D9E1F2]

-14​

[/td][td=bgcolor:#D9E1F2]

13​

[/td][td=bgcolor:#D9E1F2]

-1​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td]name04[/td][td]

19​

[/td][td]

13​

[/td][td]

26​

[/td][td]

-13​

[/td][td]

16​

[/td][td]

3​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td=bgcolor:#D9E1F2]name05[/td][td=bgcolor:#D9E1F2]

15​

[/td][td=bgcolor:#D9E1F2]

8​

[/td][td=bgcolor:#D9E1F2]

30​

[/td][td=bgcolor:#D9E1F2]

20​

[/td][td=bgcolor:#D9E1F2]

13​

[/td][td=bgcolor:#D9E1F2]

-9​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td][/td][td]name06[/td][td]

15​

[/td][td]

7​

[/td][td]

34​

[/td][td]

-27​

[/td][td]

12​

[/td][td]

-15​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td][/td][td][/td][td=bgcolor:#D9E1F2]name07[/td][td=bgcolor:#D9E1F2]

15​

[/td][td=bgcolor:#D9E1F2]

6​

[/td][td=bgcolor:#D9E1F2]

36​

[/td][td=bgcolor:#D9E1F2]

0​

[/td][td=bgcolor:#D9E1F2]

16​

[/td][td=bgcolor:#D9E1F2]

-14​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td][/td][td]name08[/td][td]

18​

[/td][td]

8​

[/td][td]

34​

[/td][td]

40​

[/td][td]

17​

[/td][td]

-9​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td][/td][td][/td][td=bgcolor:#D9E1F2]name09[/td][td=bgcolor:#D9E1F2]

11​

[/td][td=bgcolor:#D9E1F2]

0​

[/td][td=bgcolor:#D9E1F2]

20​

[/td][td=bgcolor:#D9E1F2]

-20​

[/td][td=bgcolor:#D9E1F2]

14​

[/td][td=bgcolor:#D9E1F2]

-6​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Tracking (DAYS)[/td][/tr][/table]

After Running code:

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td][/td][td][/td][td=bgcolor:#4472C4]Name[/td][td=bgcolor:#4472C4]01 2019[/td][td=bgcolor:#4472C4]02 2019[/td][td=bgcolor:#4472C4]03 2019[/td][td=bgcolor:#4472C4]04 2019[/td][td=bgcolor:#4472C4]05 2019[/td][td=bgcolor:#4472C4]06 2019[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][td=bgcolor:#D9E1F2]name02[/td][td=bgcolor:#D9E1F2]

14​

[/td][td=bgcolor:#D9E1F2]

10​

[/td][td=bgcolor:#D9E1F2]

32​

[/td][td=bgcolor:#D9E1F2]

15​

[/td][td=bgcolor:#D9E1F2]

10​

[/td][td=bgcolor:#D9E1F2]

-12​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td]name05[/td][td]

15​

[/td][td]

8​

[/td][td]

30​

[/td][td]

20​

[/td][td]

13​

[/td][td]

-9​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td][/td][td=bgcolor:#D9E1F2]name08[/td][td=bgcolor:#D9E1F2]

18​

[/td][td=bgcolor:#D9E1F2]

8​

[/td][td=bgcolor:#D9E1F2]

34​

[/td][td=bgcolor:#D9E1F2]

40​

[/td][td=bgcolor:#D9E1F2]

17​

[/td][td=bgcolor:#D9E1F2]

-9​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Tracking (DAYS)[/td][/tr][/table]

Code used:

Sub Filter_Selected_Month_Positive_Values()

    Dim ReportMonth As String, Col, ws As Worksheet, tbl As ListObject
    Set ws = Sheets("Tracking (DAYS)")
    Set tbl = ws.ListObjects(1)     [I][COLOR=#006400]'or Set tbl = ws.ListObjects("Tracking_DAYS")[/COLOR][/I]
    ReportMonth = "[COLOR=#ff0000]04 2019[/COLOR]"       [I][COLOR=#006400]  '(you will get this value from your userform)[/COLOR][/I]


[I][COLOR=#006400]'match value in header row to get table column number[/COLOR][/I]
    Col = WorksheetFunction.Match(ReportMonth, tbl.HeaderRowRange, 0)
[COLOR=#006400][I]'show all records[/I][/COLOR]
    On Error Resume Next: tbl.AutoFilter.ShowAllData: On Error GoTo 0
[I][COLOR=#006400]'filter[/COLOR][/I]
    tbl.Range.AutoFilter field:=Col, Criteria1:=">0", Operator:=xlFilterValues
    
End Sub