CODEWITHSUNDEEP
excelvba
PlanetUnknown
PlanetUnknown

Reputation: 4014

Excel macro to create new sheet only with next 14 days

So I have an excel, which has a dates field and flags.
I need to create a new sheet but only with date = today() + 14 AND where any of the flags(columns) does not have a "TRUE" string.

I know how to create a new sheet.
But I'm struggling to create a filter with next 14 days.

I recorded a macro for the next 7 days and it gave me this code, but I haven't been able to modify it for 14, 28 etc.

ActiveSheet.Range("$A$1:$AO$959").AutoFilter Field:=3, Criteria1:= _
        xlFilterNextWeek, Operator:=xlFilterDynamic
    ActiveWindow.SmallScroll Down:=-9

Upvotes: 1

Views: 142

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

You don't need a built-in date filter. You can build your own like this

ActiveSheet.Range("$A$1:$AO$959").AutoFilter Field:=3, Criteria1:= _
    "<" & Date + 14, Operator:=xlAnd

Note that VBA is very US-centric when it comes to dates. If your using this in a country with a non-US date format, you might have to change the Criteria to

Critieria1: = "<" & Format$(Date + 14, "yyyy/m/d")

or whatever date format works for you. But I would try it without the Format() first and only use that if you get odd results.

Upvotes: 0

Related Questions