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")

 

[My Aswer Is This]

Try this:

ActiveSheet.Range("$A$1:$Z$382774").AutoFilter Field:=6, Operator:= _
        xlFilterValues, Criteria2:=Range("Z1").Value

 

[MARK858]

Possibly...

Range("$A$1:$Y$382774").AutoFilter 6, Format(Range("Z1"), "m/dd/yyyy")

Important, make sure that your format is the same i.e. if your
date format is actually m/d/yyyy then it needs changing in the code.

Please note that I have only wrote the code as a Criteria1 for testing (and don't see why your line isn't showing Criteria1).

 

[richardtims]

Thanks for the response from both. Neither solutions worked. It is still bugging. Here is more code. What I am doing is essentially opening a read only copy of a large spreadsheet on our servers. I am then copying the value that is in cell A1 on my workbook saved on my desktop to cell Z1 on the large read only spreadsheet. I then want to enable the auto filter and filter column 2 and 6. Column 2 will always be the same, but column 6 will be a date that will be dynamic. I hope this explains my situation. I have pasted code below. Thanks again for your help.

Sub Macro1()
'
' Macro1 Macro
'
'
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Workbooks.Open Filename:= _
        "\\Server01\file\Daily & Weekly\2018 Call Report.xlsb"
    Sheets("Detail Data").Select
    Range("Z1").Select
    ActiveSheet.Paste
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$Z$382774").AutoFilter Field:=6, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "8/30/2018")
    ActiveSheet.Range("$A$1:$Z$382774").AutoFilter Field:=2, Criteria1:= _
        "=ALO Greensboro", Operator:=xlOr, Criteria2:="=ALO Sarasota"
        
End Sub

 

[MARK858]

When you right click one of the dates to be filtered and click format, Custom did you change the code I posted to match the format it shows in the "Type" box?

 

[richardtims]

I didn't change the format in the code, but I did change the format of A1 to the exact "M/D/YYYY HH:HH" format. When you click on Z1 on the destionation sheet, it is pasted with the exact format. They both say 8/30/2018 0:00.

 

[MARK858]

You must change the code to match the dates to be filtered as I stated in post number 3 in bold (I have re-pasted it below)...

Important, make sure that your format is the same i.e. if your
date format is actually m/d/yyyy then it needs changing in the code.

 

[richardtims]

I have made the changes and am still getting bugged at that line (See attached image). I have also attached a screenshot of both Z1 and what is in column J:J (#6). I really appreciate your help with this.

 

[MARK858]

still getting bugged at that line

What does the error state? and didn't you state that your format was "M/D/YYYY HH:HH"?

 

[richardtims]

Sorry I wasn't clear enough. This is why I thought I would use screenshots of everything as well. When you go to the filter choices, it only gives the date excluding the time. This is why I didn't include it. I know it can be difficult to properly answer questions without the necessary details.

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