I wrote a code that worked for months, suddenly, yesterday it stopped working. I am baffled as of what causes the issue.
The error occurs with the Autofilter. I get a Run-time Error '1004' AutoFilter method of Range class failed
I tried to run the macro with a given month as well Criteria1:=xlFilterAllDatesInPeriodFebruary, Operator:=xlFilterDynamic but it did not work with February but the macro run with any other month. I find it really weird.
Can anybody help me figure this out, I'm really frustrated with this one. :S This below is just an excerpt of the macro, I removed everything unnecessary (I tried running this as well and still got the error message).
Sub Autofilter()
Dim ws1 As Worksheet
Dim strUserName As String
strUserName = Environ("Username")
Dim xlmonth As Long
Dim sd As Variant
sd = Workbooks("Macro Launcher.xlsm").Worksheets("Main Sheet").Range("B2") 'contains current month
If sd = "January" Then xlmonth = xlFilterAllDatesInPeriodJanuary _
Else If sd = "February" Then xlmonth = xlFilterAllDatesInPeriodFebruary _
Else If sd = "March" Then xlmonth = xlFilterAllDatesInPeriodMarch _
Else If sd = "April" Then xlmonth = xlFilterAllDatesInPeriodApril _
Else If sd = "May" Then xlmonth = xlFilterAllDatesInPeriodMay _
Else If sd = "June" Then xlmonth = xlFilterAllDatesInPeriodJune _
Else If sd = "July" Then xlmonth = xlFilterAllDatesInPeriodJuly _
Else If sd = "August" Then xlmonth = xlFilterAllDatesInPeriodAugust _
Else If sd = "September" Then xlmonth = xlFilterAllDatesInPeriodSeptember _
Else If sd = "October" Then xlmonth = xlFilterAllDatesInPeriodOctober _
Else If sd = "November" Then xlmonth = xlFilterAllDatesInPeriodNovember _
Else If sd = "December" Then xlmonth = xlFilterAllDatesInPeriodDecember
Workbooks.Open ("C:\Users\" & strUserName & "\Downloads\DNR BIP 1_ Detail Employee Listing - Active Employee Information.xlsx")
Range("B:C,G:H,J:O,R:W,AA:AL,AQ:AW,AY:AY,BA:BG").Delete Shift:=xlToLeft
Set ws1 = Workbooks("DNR BIP 1_ Detail Employee Listing - Active Employee Information.xlsx").Sheets("Sheet1")
ws1.Range("$A:$P").Autofilter field:=10, Criteria1:="HR" '- I tried removing this one as well, still got the error
'ws1.Range("$A:$P").AutoFilter Field:=11, Criteria1:=xlFilterAllDatesInPeriodFebruary, Operator:=xlFilterDynamic -- trited this as well, did not work
ws1.Range("$A:$P").Autofilter field:=11, Criteria1:= _
xlmonth, Operator:=xlFilterDynamic '- this is the one where the macro fails
End Sub
I tried replacing ws1 with ActiveSheet, nothing seems to work. I'd really appreciate if somebody can help me out.
PS: I am not a pro, just a beginner
February is misspelled in VBA coding (not sure if it was intended or accidental) but technically I had to just change February to Februray to make the code work.