auto start filtering

[Guest]

Hi...

I'm looking for code that will go into an auto_open macro that will
automatically filter column B6:B2000 to the value todays date [if the range
has a cell with todays value in it]. If the range doesn't have todays date in
it then no action is taken. It sounds simple but I'm struggling...

Any help appreciated...

Gordon.

 

[Debra Dalgleish]

Create a macro that filters for today's date. Then, in the ThisWorkbook
module, add code to the Workbook_Open event, to run that macro.

'==========================
Private Sub Workbook_Open()
FilterToday
End Sub
'===========================

'================================
'this procedure is stored in a regular module
Sub FilterToday()
Dim rng As Range
Dim rngF As Range
Dim ws As Worksheet
Dim LastRow As Long
Dim strDate As String
strDate = Format(Date, "m/d/yy")
Set ws = Sheets("Pivot Data")

'remove any existing filter
If ws.AutoFilterMode Then
ws.Cells.AutoFilter
End If

With ws
.Range("A1").AutoFilter Field:=1, _
Criteria1:=strDate
End With

Set rng = ws.AutoFilter.Range

Set rngF = Nothing
On Error Resume Next
With rng
'count the rows
Set rngF = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
End With
On Error GoTo 0

If rngF Is Nothing Then
ws.Cells.AutoFilter
End If

End Sub
'========================

Hi...

I'm looking for code that will go into an auto_open macro that will
automatically filter column B6:B2000 to the value todays date [if the range
has a cell with todays value in it]. If the range doesn't have todays date in
it then no action is taken. It sounds simple but I'm struggling...

Any help appreciated...

Gordon.