Advanced Filter using a date as the criteria

[jaded62]

Hi All,

I am trying to do a simple Advanced Filter that filters for one date.

The search date is in the format of dd/mm/yyyy as are all of the dates in the data range.

Running the code below returns all dates, not just the date I'm filtering for.

I have tried formating the data and search date to mm/dd/yyyy, yyyy/dd/mm, and as a serial number. All formats return all dates.

Guidance would be greatly appreciated!

Cheers.

Set wsData = wsTimesheet_Data 'Sets a reference to the data WS.
   Set wsSO = wsSAP_Operations 'Sets a reference to the SAP Ops WS.
   Set RangeResStart = wsSAP_Operations.Range("A12") 'Sets a reference to the cell A12 in the SAP Operations WS.

   Set RangeTrans = wsData.Range("TS_Data") 'Selects all the date data by Setting the range to the date colum of the data WS.
   
   Application.ScreenUpdating = False
      
   RangeResStart.CurrentRegion.ClearContents 'Clear any existing data from the search result.
        
  'Runs the advanced filter using the above criteria and copies (using the Filtercopy action) the outcome to the SAP Ops tab.
  
  RangeTrans.AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=wsSO.Range("SAP_Date1"), _
        CopyToRange:=RangeResStart, _
        Unique:=False

 

[jaded62]

I should add too that setting Unique to True makes no difference.

 

[RoryA]

What is the range of SAP_Date1 and what exactly is in there?

 

[Alex Blakenburg]

1) Does TS_Data include the heading row of the data to be filtered ?
If not expand the range to include the heading.
2) Does SAP_Date1 have the exact same heading as TS_Data (the column for the filter) in the first row and the date in the 2nd row ?

 

[jaded62]

Headers? Of course not! In my rush to finish this before the xmas break I forgot all about that requirement.

Thanks!

 

[Alex Blakenburg]

No problem. Glad we could help. Have a good Christmas.