VBA date filter returns blank

[mahmed1]

Hey I am trying to amend the date which is hard coded to the cell that is on the
import sheet range C2 (I have named that cell Date_Filter) but when i try to amend code it keeps returning a blank

Trying to amend where it says amend this

Date From

27/09/2023

Sub Data()


    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Dim ImportSh As Worksheet
    Dim Lrow As Long
   
    Set ws = ThisWorkbook.Worksheets("Extract")
    Set ws2 = ThisWorkbook.Worksheets("Filter")
    Set ImportSh = ThisWorkbook.Worksheets("Import")
   
    Application.ScreenUpdating = False
   
   
    Lrow = ws.Range("A" & Rows.Count).End(xlUp).Row
   
    Application.Goto ws.Range("A1"), True


    ws.ListObjects("Table2").Range.AutoFilter Field:=10, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "9/27/2023") <<<< amend this
       
       
    ws.Range(Cells(1, 6), Cells(Lrow, 6)).SpecialCells(xlCellTypeVisible).Copy
    ws2.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
   
    Lrow = ws2.Range("A" & Rows.Count).End(xlUp).Row
 
    ws2.Range("$A$1:$A$" & Lrow).RemoveDuplicates Columns:=1, Header:=xlYes
   
    ws.ListObjects("Table2").Range.AutoFilter
    ws.ListObjects("Table2").Range.AutoFilter
   
    Lrow = ws2.Range("A" & Rows.Count).End(xlUp).Row
   
    ws2.Range("A1:A" & Lrow).Copy
    ws.Range("M1").PasteSpecial xlPasteValuesAndNumberFormats
   
    ws.Range("Table2[#All]").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=ws.Range("M1:M" & Lrow), _
    CopyToRange:=ws.Range("Q1:Z1"), _
    Unique:=False
   
    Lrow = ws.Range("Q:Z").Find(WHat:="*", After:=ws.Range("Q1"), SearchDirection:=xlPrevious).Row
    ws.ListObjects("Table7").Resize ws.Range("Q1:Z" & Lrow)
   
    Application.ScreenUpdating = True
   
End Sub

 

[Micron]

Try replacing quotes with date delimiters - #09/27/2023#
Your underlying data might be formatted as date and not string data type.

 

[mahmed1]

Hey I do I add the # in vba code by referencing a cell

Ie

Importsh.range(#”C2”#).value

 

[Micron]

Your example is incomplete because you show nothing that assigns that expression to any variable. So it may be that you would have to concatenate a variable with delimiters (e.g. myDate = "#" & Importsh.Range(C2) & "#") or perhaps Excel vba has data type conversion functions as does M$ Access, which I'm more familiar with.
That might be doable as myDate = CDate(Importsh.Range(C2))

AFAIK, data delimiters cannot be used in the range address property as you demonstrated.
BTW, "Hey" is a rather informal way of addressing someone and may be OK with someone you know. When you don't know the person(s) you are addressing, some consider it too informal and bordering on rude. Just saying...