PDA

View Full Version : Solved: AutoFilter, Criteria, Header



Philcjr
05-24-2010, 02:03 PM
I need help in getting the AutoFilter Criteria into the Header of the worksheet.

Need AutoFilter ("G3") Criteria in Header (Left Justified)
Need AutoFilter ("H3") Criteria in Header (Centered)
Need AutoFilter ("I3") Criteria in Header (Right Justified)

any help would be greatly appreciated,
Phil

Philcjr
05-24-2010, 02:21 PM
I know this is not correct, but this is what I have thus far:


Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Accounts Data" Then
With ActiveSheet.PageSetup
.LeftHeader = Range("G3").AutoFilter.Filters.Criteria1
.CenterHeader = Range("H3").AutoFilter.Filters.Criteria1
.RightHeader = Range("I3").AutoFilter.Filters.Criteria1
End With
End If
End Sub

Zack Barresse
05-24-2010, 03:33 PM
Hiya!

Stephen Bullen wrote a function a while back to get the criteria for the filter of a range. Here is the code you need (the function would go into it's own standard module)...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Accounts Data" Then
With ActiveSheet.PageSetup
.LeftHeader = FilterCriteria(Range("G3"))
.CenterHeader = FilterCriteria(Range("H3"))
.RightHeader = FilterCriteria(Range("I3"))
End With
End If
End Sub

Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function

Philcjr
05-24-2010, 03:51 PM
Hi Zac,
this is phil's mom - thank you, thank you

this was wonderful and i really appreciate your assistance

Zack Barresse
05-24-2010, 03:52 PM
You're very welcome Phil's mom!! :) Glad I could be of service.