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

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​
2​			Name	01 2019	02 2019	03 2019	04 2019	05 2019	06 2019
3​			name01	10​	7​	30​	-23​	18​	-5​
4​			name02	14​	10​	32​	15​	10​	-12​
5​			name03	19​	14​	28​	-14​	13​	-1​
6​			name04	19​	13​	26​	-13​	16​	3​
7​			name05	15​	8​	30​	20​	13​	-9​
8​			name06	15​	7​	34​	-27​	12​	-15​
9​			name07	15​	6​	36​	0​	16​	-14​
10​			name08	18​	8​	34​	40​	17​	-9​
11​			name09	11​	0​	20​	-20​	14​	-6​
12​
13​

Sheet: Tracking (DAYS)

After Running code:

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​
2​			Name	01 2019	02 2019	03 2019	04 2019	05 2019	06 2019
4​			name02	14​	10​	32​	15​	10​	-12​
7​			name05	15​	8​	30​	20​	13​	-9​
10​			name08	18​	8​	34​	40​	17​	-9​
12​
13​

Sheet: Tracking (DAYS)

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