Amend Small Macro

[Chris_010101]

Hello,

I have the below assigned to filter buttons which automatically filter a list, based on the criterial. For "office" I also need it to filter "call centre" in addition. So when the "office" button is pressed, it also filters call centre with office.

Sub Office()
'
' Office Macro
'

'
    ActiveSheet.Range("$A$1:$D$46").AutoFilter Field:=1, Criteria1:="*Office*"
    ActiveWindow.SmallScroll Down:=-6
End Sub
Sub Yard()
'
' Yard Macro
'

'
    ActiveSheet.Range("$A$1:$D$46").AutoFilter Field:=1, Criteria1:="*Yard*"
    ActiveWindow.SmallScroll Down:=-6
End Sub
Sub Transport()
'
' Transport Macro
'

'

    ActiveSheet.Range("$A$1:$D$46").AutoFilter Field:=1, Criteria1:="*Transport*"
    ActiveWindow.SmallScroll Down:=-6
End Sub

Many Thanks

 

[Georgiboy]

Are you able to put the data inside a table object (CTRL+T)?

If you can then you could add a slicer to the table and you would not need macros/ buttons. Unless you have many variations of strings with the word 'yard' in the middle for example.

 

[Chris_010101]

Are you able to put the data inside a table object (CTRL+T)?

If you can then you could add a slicer to the table and you would not need macros/ buttons. Unless you have many variations of strings with the word 'yard' in the middle for example.

I would usually look at doing something like this but the managers who use the document are used to being able to press the button to see the data they need - and I can't therefore change it. It's just not including "call centre" in with "office", so the data in the filter is never truly accurate.

 

[Georgiboy]

I see, slicers are basically a group of buttons that change what data is displayed in the table. IMO they are neater than a button option.

Code option might be:

Sub Office()
'
' Office Macro
'

'
    ActiveSheet.Range("$A$1:$D$46").AutoFilter Field:=1, Criteria1:="*Office*", Operator:=xlOr, Criteria2:="*call centre*"
End Sub

 

[Chris_010101]

I see, slicers are basically a group of buttons that change what data is displayed in the table. IMO they are neater than a button option.

Code option might be:

Sub Office()
'
' Office Macro
'

'
    ActiveSheet.Range("$A$1:$D$46").AutoFilter Field:=1, Criteria1:="*Office*", Operator:=xlOr, Criteria2:="*call centre*"
End Sub

Thanks very much

I added with the ActiveWindow.SmallScroll Down:=-6 at the bottom and it works