Autofilter using a value from a list.
 

Hi,

I need to autofilter 2 lists on 2 sheets (Main & Revenue Costs).

Main's sheet is cells B4 - AH4 for the headings, list is "n" rows long

Revenue Costs sheet is cells A4 - M4 for the headings, again "n" rows long.

I need them to filter when a button a sheet (named "Front") is selects.

The value will be in Cell I6 of the "Front" sheet.

Is this possible?

Thanks in advance for any help with the VBA code.

Autofilter using a value from a list.
 

Hi NIKO,

Use an ActiveX button on Sheet Front. If using xl2007 then on Developer
ribbon select Insert in the Controls block and select from the ActiveX
controls. If earlier version of xl then select button from the Controls
Toolbox Toobar (Not from the Forms toolbar). Design mode will switch on (Set
square, ruler and pencil in the button icon).

Right click the button while in Design mode and select View code. A default
sub name will appear for the click event on the button. This is the sub name
that must be used for the first sub of the following code so if the sub name
I have used does not match then use your default one.

Copy the following code into the VBA editor. Note there are 2 subs. The
first one belongs in the button click sub; the second one leave out on its
own below the first sub.

You will need to edit the filtNumb to the filter you want set. Count the
filters from the left for the number of the filter to set.

Close the VBA editor. (X with red background top right of VBA editor screen.)

On the worksheet, Click the Design mode button to tun it off. (Should change
from orange to blue.)

To get back to the code, right click the worksheet name tab and select View
code.

Private Sub CommandButton1_Click()

Dim crit1 As Variant 'Holds criteria cell values
Dim filtNumb As Integer 'Holds filter number to set

crit1 = Sheets("Front").Range("I6")

'Edit filter number to suit
'count filters from left for number.
filtNumb = 1

Call SetFilters("Main", crit1, filtNumb)

Call SetFilters("Revenue Costs", crit1, filtNumb)

End Sub


Sub SetFilters(strShtName, crit, filt)

With Sheets(strShtName)
'Ensure that AutoFilter is turned on.
'This avoids error problems if not turned on.
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData 'Remove existing filtering
End If
With .AutoFilter.Range
.AutoFilter Field:=filt, Criteria1:="=" & crit
End With
Else
MsgBox "AutoFilter not turned on for sheet " _
& strShtName & "." & vbCrLf & _
"Processing for sheet " & strShtName & " terminated."
End If
End With

End Sub

--
Regards,

OssieMac

Autofilter using a value from a list.
 

Thanks Ossie, I've had to tweak it abit as the filter on the 2nd sheet is in
a different place to the first but apart from that it works perfectly.

Appreciated.

"OssieMac" wrote:

Hi NIKO,

Use an ActiveX button on Sheet Front. If using xl2007 then on Developer
ribbon select Insert in the Controls block and select from the ActiveX
controls. If earlier version of xl then select button from the Controls
Toolbox Toobar (Not from the Forms toolbar). Design mode will switch on (Set
square, ruler and pencil in the button icon).

Right click the button while in Design mode and select View code. A default
sub name will appear for the click event on the button. This is the sub name
that must be used for the first sub of the following code so if the sub name
I have used does not match then use your default one.

Copy the following code into the VBA editor. Note there are 2 subs. The
first one belongs in the button click sub; the second one leave out on its
own below the first sub.

You will need to edit the filtNumb to the filter you want set. Count the
filters from the left for the number of the filter to set.

Close the VBA editor. (X with red background top right of VBA editor screen.)

On the worksheet, Click the Design mode button to tun it off. (Should change
from orange to blue.)

To get back to the code, right click the worksheet name tab and select View
code.

Private Sub CommandButton1_Click()

Dim crit1 As Variant 'Holds criteria cell values
Dim filtNumb As Integer 'Holds filter number to set

crit1 = Sheets("Front").Range("I6")

'Edit filter number to suit
'count filters from left for number.
filtNumb = 1

Call SetFilters("Main", crit1, filtNumb)

Call SetFilters("Revenue Costs", crit1, filtNumb)

End Sub


Sub SetFilters(strShtName, crit, filt)

With Sheets(strShtName)
'Ensure that AutoFilter is turned on.
'This avoids error problems if not turned on.
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData 'Remove existing filtering
End If
With .AutoFilter.Range
.AutoFilter Field:=filt, Criteria1:="=" & crit
End With
Else
MsgBox "AutoFilter not turned on for sheet " _
& strShtName & "." & vbCrLf & _
"Processing for sheet " & strShtName & " terminated."
End If
End With

End Sub

--
Regards,

OssieMac