VBA code to select dates used to work, but now broken

[dotwil]

I have a vba script that used to work by selecting a date range from some data. Now it does not select anything anymore. While debugging I realised that if I run the code to the point where the selection of <= Filterdate takes place and then go to the sheet, click on the dropdown selection, the criteria is correct. By pressing OK on the dialogue, the selection then takes place and the rest of the macro executes correctly.

It used to work, but now it does not anymore. Any help would be appreciated.

[pre]

Sub OverdueProjectsGrab()
'
' OverdueProjectsGrab Macro
'

'
Dim lastrow As Long
Dim FilterDate As Date
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A6:U" & lastrow).ClearContents
Range("B2").Select
FilterDate = ActiveCell.Value
Sheets("Data").Select
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
ActiveSheet.Range("$A$1:$U" & lastrow).AutoFilter Field:=14, Criteria1:="="
ActiveSheet.Range("$A$1:$U" & lastrow).AutoFilter Field:=12, Criteria1:= _
"<=" & FilterDate, Operator:=xlAnd
ActiveSheet.Range("$A$1:$U" & lastrow).AutoFilter Field:=11, Criteria1:=">1"
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Range("A2:U" & lastrow).Select
Selection.Copy
Sheets("Overdue Projects").Select
Range("A6").Select
ActiveSheet.Paste
Sheets("Data").Select
On Error Resume Next
ActiveSheet.ShowAllData
Err.Clear
Sheets("Overdue Summary").PivotTables("PivotTable1").RefreshTable
Sheets("Overdue Projects").Select
Range("B2").Select
End Sub

[/pre]

 

[Hui]

Dotwil


You may want to consider adding the following code before the first Activesheet.Range line

It will clear the existing filters

[pre]

If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If

[/pre]

 

[dotwil]

Thanks, I was wondering how to do that, but it still does not solve the problem. I changed the one line to make sure that 'FilterDate' is not causing this. It is not.

'ActiveSheet.Range("$A$1:$U" & lastrow).AutoFilter Field:=12, Criteria1:= _

"<=30/11/2012", Operator:=xlAnd

Still if I debug to this line and then manually click filter and 'OK' in the dialogue box then carry on with the code, it works. Strange thing is that it used to work.

Could it maybe be difference in number formatting? I have checked that to be the same.

 

[dotwil]

Here's the thing. It works on another computer with the same Excel version.

So then I changed my computer date style from dd-mmm-yyy to yyyy-mm-dd to match the computer where the code works. Voila, now the code works again and I am happy.

What is the explanation, though? Makes portability very difficult.

Any suggestions on how to improve the code to prevent this bug?

 

[NARAYANK991]

Hi ,


Instead of using the following :


Criteria1:= "<=30/11/2012"


can you try using :


Criteria1:= "<=" & DateSerial(year , month , day)


since the DateSerial function will return a date in the proper format required by the computer where the code is running.


If the above does not work because a string is required , try using :


Criteria1:= "<=" & Format(Dateserial(year,month,day),"Short Date")


Narayan