Printing Headers using Criteria chosen from AutoFilter
 

Can I create a way to have the headers of a report change by the criteria
chosen from an Auto Filter Field?

I have a worksheet with a generic print button for users. I would like to
have the header change depending on which A1 Field the user chose; i.e.,
Utah, Montana, California, etc.

Is it possible.

Printing Headers using Criteria chosen from AutoFilter
 

Sondra

The basic syntax would be

Sheet1.PageSetup.CenterHeader = Sheet1.AutoFilter.Filters(1).Criteria1

I think Filters(1) will refer to the first column of your fitlered range, so
it should work for A1 in your case. This will show things like

=Utah for equal to Utah
=*Utah for ends with Utah
=*Utah* for contains Utah
<Utah for does not equal Utah

You might want to get all those operators out of there. Here's a procedure
that does that

Dim sCrit As String
Dim vOpers As Variant
Dim i As Long

vOpers = Array("=", "*", "<", "")

sCrit = Sheet4.AutoFilter.Filters(1).Criteria1
For i = LBound(vOpers) To UBound(vOpers)
sCrit = Replace(sCrit, vOpers(i), "")
Next i
Sheet4.PageSetup.CenterHeader = sCrit

The results may be misleading though. If someone chooses 'does not equal
Utah' the header will say 'Utah'. It will also remove any equal signs, etc.
from within the text, but that doesn't sound like it will be a problem for
you. Finally, it doesn't make sure there's a filter, so you might want to
check the FitlerMode property of the sheet first.

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com
Sondra wrote:
Can I create a way to have the headers of a report change by the
criteria chosen from an Auto Filter Field?

I have a worksheet with a generic print button for users. I would
like to have the header change depending on which A1 Field the user
chose; i.e., Utah, Montana, California, etc.

Is it possible.

Printing Headers using Criteria chosen from AutoFilter
 

The results may be misleading though. If someone chooses 'does not
equal Utah' the header will say 'Utah'. It will also remove any
equal signs, etc. from within the text, but that doesn't sound like
it will be a problem for you. Finally, it doesn't make sure there's
a filter, so you might want to check the FitlerMode property of the
sheet first.

Make that AutoFilterMode, not FilterMode. And you should also check

Sheet1.AutoFilter.Filters(1).On

to ensure that filter was set on that column.

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com