Hi all,
I've created a macro that autofilters everything within the next 7 days of the current date along with several other criteria. It works perfectly on my computer in the US, but my UK counterpart seems to come up with incorrect rows. My guess is that it is a date formatting issue, but I'm not sure how to get around the problem. I have posted the macro below.
I've created a macro that autofilters everything within the next 7 days of the current date along with several other criteria. It works perfectly on my computer in the US, but my UK counterpart seems to come up with incorrect rows. My guess is that it is a date formatting issue, but I'm not sure how to get around the problem. I have posted the macro below.
Code:
Private Sub Upcoming_Bids_Click()
Dim MyDate As String
Dim MyDatePlus As String
MyDate = Now()
MyDatePlus = DateAdd("d", 7, Now())
ActiveSheet.Unprotect
Selection.AutoFilter Field:=1, Criteria1:="=Propose to Bid", Operator:=xlOr, Criteria2:="=Propose to Re-Bid"
Selection.AutoFilter Field:=21, Criteria1:="=PLC", Operator:=xlOr, Criteria2:="=SBU"
Selection.AutoFilter Field:=14, Criteria1:=">=" & CStr(MyDate), Operator:=xlAnd, Criteria2:="<=" & CStr(MyDatePlus)
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End Sub