Autofilter
 

Hello I have a sheet which contains a large amount of data, this data is
broken down into different departments; the departments are known by the
first two numbers, so 16000 is department A and 17000 is department B.

For examples sake I have three buttons, one button allows you to view the
sheet with everything unfiltered, one button is just to filter out everything
but 16 and the other to just display 17.

At the moment I'm doing it with the following VBA Code -


Private Sub filterSheet(dep As Integer, sht As String)
Sheets(sht).Select
Columns("A:M").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=" & dep & "0000",
Operator:=xlAnd, Criteria2:="<" & dep + 1 & "0000"
End Sub

This does what I want it to, but the problem comes with the line
Selection.AutoFilter
Because the button that takes me to the sheet to view ALL the departments,
doesn't use an Autofilter, yet the BACK button on this sheet uses the line
Selection.Autofilter to disable the autofilter and if it's not been set then
I get a problem where it becomes disabled when it should be active.

I'm just wondering if there is a way to specify Autofilter = False or
something like that?

I do hope that made some sense, as I think it gets quite muddled towards the
middle :)

Thanks for any help in advance!

Autofilter
 

"I'm just wondering if there is a way to specify Autofilter = False or
something like that?"

Worksheets("Sheet1").AutoFilterMode = False

Alan



"NateBuckley" wrote:

Hello I have a sheet which contains a large amount of data, this data is
broken down into different departments; the departments are known by the
first two numbers, so 16000 is department A and 17000 is department B.

For examples sake I have three buttons, one button allows you to view the
sheet with everything unfiltered, one button is just to filter out everything
but 16 and the other to just display 17.

At the moment I'm doing it with the following VBA Code -


Private Sub filterSheet(dep As Integer, sht As String)
Sheets(sht).Select
Columns("A:M").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=" & dep & "0000",
Operator:=xlAnd, Criteria2:="<" & dep + 1 & "0000"
End Sub

This does what I want it to, but the problem comes with the line
Selection.AutoFilter
Because the button that takes me to the sheet to view ALL the departments,
doesn't use an Autofilter, yet the BACK button on this sheet uses the line
Selection.Autofilter to disable the autofilter and if it's not been set then
I get a problem where it becomes disabled when it should be active.

I'm just wondering if there is a way to specify Autofilter = False or
something like that?

I do hope that made some sense, as I think it gets quite muddled towards the
middle :)

Thanks for any help in advance!