Amend Small Macro

Rob_010101

Board Regular
Joined
Jul 24, 2017
Messages
198
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.
 
Upvote 0
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.
 
Upvote 0
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:
VBA Code:
Sub Office()
'
' Office Macro
'

'
    ActiveSheet.Range("$A$1:$D$46").AutoFilter Field:=1, Criteria1:="*Office*", Operator:=xlOr, Criteria2:="*call centre*"
End Sub
 
Upvote 0
Solution
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:
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,954
Messages
6,175,603
Members
452,658
Latest member
GStorm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top