AutoFilter Field Macro Returns a Header Row only

[NorthbyNorthwest]

Hi, everyone. I have a worksheet and code below. See pic attached. I cannot get auto filter to work. Every time I run macro it returns the header row only. Could someone help me? I've tried checking various columns and the code itself, and I come up with nothing.

Sub FilterByMonth()
Dim MyRange As Range
Dim CalcMode As Long
Dim ViewMode As Long
Dim lrow As Long
Dim FilterCriteria As String
Dim FilterCriteria2 As String
Dim NewBook As Workbook

'Filter MTD Accuracy
Sheet1.Activate
Sheet1.Unprotect "hideaway"
lrow = Cells.Find(What:="*", After:=Range("B7"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, MatchCase:=False).Row

Set MyRange = Range(Cells(6, 2), Cells(lrow, 13))
MyRange.Parent.Select

If ActiveWorkbook.ProtectStructure = True Or MyRange.Parent.ProtectContents = True Then
    MsgBox "Sorry, not working when the workbook or worksheet is protected", vbOKOnly, "Remove Protection"
    Exit Sub
End If

'Change ScreenUpdating, Calculation, EnableEvents, ....
With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
End With
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
ActiveSheet.DisplayPageBreaks = False

'Firstly, remove the AutoFilter
MyRange.Parent.AutoFilterMode = False

'Filter on a Inputbox value 1
FilterCriteria = InputBox("What month do you want to filter on?", "Enter the fiscal month number 1 thru 12.")

'Filter on a Inputbox value 2
FilterCriteria2 = InputBox("What audit group do you want to filter on?", "Enter either *Alpha* or *Beta*.")

With MyRange
    .AutoFilter Field:=12, Criteria1:="=" & FilterCriteria
    .AutoFilter Field:=5, Criteria1:="=" & FilterCriteria2
End With

Set NewBook = Workbooks.Add
ThisWorkbook.Sheets("MTD Accuracy").AutoFilter.Range.Copy
    With NewBook.Worksheets("Sheet1")
        .Range("B3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        .Range("B3").PasteSpecial Paste:=xlPasteFormats
        .Columns("A").ColumnWidth = 5
        .Columns("B").ColumnWidth = 10
        .Columns("C:D").ColumnWidth = 35
        .Columns("E").ColumnWidth = 10
        .Columns("F").ColumnWidth = 19
        .Columns("G").ColumnWidth = 10
        .Columns("H").ColumnWidth = 22
        .Columns("I:L").ColumnWidth = 13
        .Columns("M").ColumnWidth = 10
        .Columns("M").Hidden = True
        .Rows("3:3").RowHeight = 46
        .Rows("4:204").RowHeight = 20
        .Range("A1").Select
    End With
Application.CutCopyMode = False
ActiveWindow.DisplayGridlines = False
Sheet1.Protect "hideaway"
End Sub

Thanks,

 

[MARK858]

What happens with

.AutoFilter Field:=4, Criteria1:="=" & FilterCriteria2

 

[NorthbyNorthwest]

What happens with

.AutoFilter Field:=4, Criteria1:="=" & FilterCriteria2

It filtered the blank rows. Hide rows with data.

 

[MARK858]

I think we will need your data posted with XL2BB or your file hosted somewhere rather than an image because
with the code below

Sub FilterByMonth()
Dim MyRange As Range
Dim lrow As Long
Dim FilterCriteria As String
Dim FilterCriteria2 As String


lrow = Cells.Find(What:="*", After:=Range("B7"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, MatchCase:=False).Row

Set MyRange = Range(Cells(6, 2), Cells(lrow, 13))
MyRange.Parent.Select

'Firstly, remove the AutoFilter
MyRange.Parent.AutoFilterMode = False

'Filter on a Inputbox value 1
FilterCriteria = InputBox("What month do you want to filter on?", "Enter the fiscal month number 1 thru 12.")

'Filter on a Inputbox value 2
FilterCriteria2 = InputBox("What audit group do you want to filter on?", "Enter either *Alpha* or *Beta*.")

With MyRange
    .AutoFilter Field:=12, Criteria1:="=" & FilterCriteria
    .AutoFilter Field:=4, Criteria1:="=" & FilterCriteria2
End With

End Sub

and the data below

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
6	Employee	Position	Grade	Audit Group	Unit	Supervisor	Audit Month	Total	Checked	Correct	Rate	Fiscal Month
7				Alpha								8
8				Alpha								7
9				Beta								8
10				Alpha								8
11				Beta								7
12				Alpha								7
13				Alpha								8
MySheet

With FilterCriteria as 8 and FilterCriteria2 as Alpha I get

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
6	Employee	Position	Grade	Audit Group	Unit	Supervisor	Audit Month	Total	Checked	Correct	Rate	Fiscal Month
7				Alpha								8
10				Alpha								8
13				Alpha								8
MySheet

 

[MARK858]

LookIn:=xlFormulas

Are your cells values or are any columns formulas?

 

[NorthbyNorthwest]

LookIn:=xlFormulas

Are your cells values or are any columns formulas?

The entire worksheet is formulas.

 

[MARK858]

Then I'll definitely need the worksheet posted either with XL2BB or a file hosted somewhere if the below returns the correct last row

Sub FilterByMonth()
Dim lrow As Long

lrow = Cells.Find(What:="*", After:=Range("B7"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, MatchCase:=False).Row

MsgBox "The last row is  " & lrow

End Sub

 

[NorthbyNorthwest]

Then I'll definitely need the worksheet posted either with XL2BB or a file hosted somewhere if the below returns the correct last row

Sub FilterByMonth()
Dim lrow As Long

lrow = Cells.Find(What:="*", After:=Range("B7"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, MatchCase:=False).Row

MsgBox "The last row is  " & lrow

End Sub

Yes, I am not getting the correct last row. I will try uploading worksheet.

 

[NorthbyNorthwest]

Then I'll definitely need the worksheet posted either with XL2BB or a file hosted somewhere if the below returns the correct last row

Sub FilterByMonth()
Dim lrow As Long

lrow = Cells.Find(What:="*", After:=Range("B7"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, MatchCase:=False).Row

MsgBox "The last row is  " & lrow

End Sub

MARK858, thanks for help. After some frustration, I discovered the problem was in a column reference. I referenced the wrong one.

 

[MARK858]

MARK858, thanks for help. After some frustration, I discovered the problem was in a column reference. I referenced the wrong one.

Isn't that what I stated in the code line I posted where I changed the 5 to a 4? Don't know why you aren't just using A1 or the last cell in your lrow line though