VBA / AutoFilter Based on Cell Value

[richardtims]

Hello,

I have tried searching for a solution for my problem within the forums, but have been unsuccessful. I hope that this is not a duplicate thread, but if so, please let me know.

I am still pretty new to VBA and hopefully this is not too much of a Rookie question. I am trying to set an auto filter to filter out a specific date that would be a value in cell Z1 on the active worksheet. I have been playing around with it but can get it to actually work. The part that I need to replace is the "8/30/2018" portion of the code. It needs to be the date that is in cell Z1. Please let me know if you have a solution. Thanks.

   ActiveSheet.Range("$A$1:$Z$382774").AutoFilter Field:=6, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "8/30/2018")

 

[MARK858]

First of all the code needs to match exactly the format of the cells (as I have already stated in 2 posts) not what you see in the autofilter.

Can you please copy and paste your current code in the thread as you did in post number 4 as we cannot copy the code from an image for testing.

Btw, from your image it looks like your date format is m/d/yyyy hhhh not m/dd/yyyy hhhh. Can you please check this as per my previous post (post number 5).

 

[richardtims]

Here is the most recent code I have......

Sub Macro2()
'
' Macro2 Macro
'
    Range("N3").Select
    Selection.Copy
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Workbooks.Open Filename:= _
        "\\dtwess11\sch_fore\Stats - Daily & Weekly\2018 Call Duration Report_Vendor.xlsb"
    Sheets("Detail Data").Select
    Range("Z1").Select
    ActiveSheet.Paste
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$X$1382774").AutoFilter 6, Format(Range("Z1"), "m/dd/yyyy")
    ActiveSheet.Range("$A$1:$X$1382774").AutoFilter Field:=1, Criteria1:=Array( _
        "CE ALO ALO Greensboro", "CW ALO ALO Greensboro", "CW ALO ALO Sarasota", _
        "FL ALO ALO Greensboro", "MW ALO ALO Greensboro", "MW ALO ALO Sarasota"), _
        Operator:=xlFilterValues
'
End Sub

 

[Peter_SSs]

The error message reads "Run-time error '1004': Application-defined or object-defined error.

You would be getting that message with the code below because you are trying to filter a range bigger than an excel worksheet that only contains 1,048,576 rows, not the 1,382,774 you have in that code.

.

 

[richardtims]

First, Can I go hide in a hole, LOL. I can't believe I didn't see that. I am still getting an error message however, but it is different this time. It is "AutoFilter method of Range class failed", but it is still at the same line.

 

[MARK858]

Have you changed your format in your code to exactly match the format in the cells being filtered yet?

 

[richardtims]

Thanks to all who helped me with this. Problem is solved. After playing around with the formatting piece and lots of trial and error, it finally worked. Thanks!!!!!

 

[MARK858]

Happy you managed to work it out