Date Filters Formats HELP!!!!!!!!!

[haybie]

I am in real trouble. I have been searching endlessly for a solution and have gotten nowhere. Basically I have a report that needs to be updated every month on the redemptions a website has orders through. I receive a datasheet that has basically all the data before hand from where it all started but I cannot copy and paste it all as it has cancellations and other status in the past that were manually changed and is not in the system.

Report 1 is called “Redemptions” all the history before hand
Report 2 is the new report of all the redemptions called “Datasheets”

I have already copied the “datasheets” into the same workbook as the “redemptions” to make things easier. My macro basically finds the max date of Redemptions. I then uses that max date to filter the “datasheets” so basically greater than max date so it only shows the new items that has come in.

The main problem I am getting is formatting. It is forever switching. I cannot get the right date. I have used so many different tries. What I don’t understand is that when I step into the process it shows that max date the right format and changes on the last minute.

Here is the main part of the macro

Sub FilterDate()
Application.ScreenUpdating = False
Dim maxDate As Date
Dim endRow As Long
With Sheets("redemptions")

endRow = .Range("H" & Rows.Count).End(xlUp).Row

'Define unique list of items in column H not sure this needed but kept just incase
.Range("H2:H" & endRow).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set rngUniques = .Range("H2:H" & endRow).SpecialCells(xlCellTypeVisible)
.ShowAllData
'Find the max date in H in redemptions report
maxDate = (Application.Max(.Range("H2:H" & endRow).SpecialCells(xlCellTypeVisible)))

Sheets("DataSheets").Select

'Filter on max date in column R go to “datasheets to filter”


Application.ScreenUpdating = True


End With
End Sub


Range("R1:R" & endRow).AutoFilter Field:=1, Criteria1:=Array(2, maxDate)

'this is exactly what I want but I want it to be greater than


I have also tried:

R ange("R1:R" & endRow).AutoFilter Field:=1, Criteria1:=">" & maxDate

'this one gives me a U.S format

Range("R1:R" & endRow).AutoFilter Field:=1, Criteria1:=">" & Format(maxDate, "dd/mm/yyyy hh:mm:ss")

'this one gives me a U.S format

Range("R1:R" & endRow).AutoFilter Field:=1, Criteria1:=">" & Format(maxDate, "dd.mm.yyyy hh.mm.ss")

'Gives me the right order but the no matches


On another query, I have used the Array(2,date) function on another macro and that works just find as I want it to equal today’s date. However when I added it to the ribbon as my own function on my excel it changed to U.s Format.
Using the same code but started it from the macros ribbon it works fine. I just don’t understand why. Any help would be much appreciated.
I would send you an excel sheet but I am new on here and I don't know how.

 

[MARK858]

Try

Range("R1:R" & endRow).AutoFilter Field:=1, Criteria1:=">" & Format(CDate(maxdate), "dd/mm/yyyy hh:mm:ss")

 

[haybie]

Thanks for the quick repoonse. I am afraid it frustratingly gives me a U.S format....