Jan 06 2020 08:19 PM - edited Jan 06 2020 08:28 PM
Hi,
I have a series of data which is linked to one tab. I have seperated some information from this tab. I am hoping then i can add to the Macro to automatically filter results. For example i only want results which were Yes in Column H to be included.
Can someone please assist.
Many thanks,
Jan 07 2020 04:29 PM
Hi All,
I have found the below. Will this work for the requirements below?
Sub filter_on_department() Dim range_to_filter As Range Set range_to_filter = Range("H:H") range_to_filter.AutoFilter Field:=1, Criteria1:="Yes" End Sub
Jan 07 2020 09:12 PM
SolutionYou need only one line of code to filter the column H which is like this...
Sub filter_on_department()
Range("A1").CurrentRegion.AutoFilter field:=8, Criteria1:="Yes"
End Sub
Btw, you have placed the event codes on Standard Modules which is not the right place for them.
You should place the Worksheet_Change event code on the Sheet Module and Workbook_Open event code on ThisWorkbook Module.
Jan 08 2020 02:57 PM
Thank kindly for your assistance. I have updated the worksheets.
Thanks again, always much appreciated.
Feb 17 2020 04:00 PM
Thanks for your help with the filter.
I notice this works when i click "Run", however is there a way to make it "Run" whenever new data is added?
Many thanks,
Feb 17 2020 05:33 PM
Which sheet do you want to filter based on the "Yes" criteria in Column H?
Are you entering "Yes" manually in column H or it is being populated with the help of a formula?
Feb 17 2020 06:40 PM
Thanks for the reply.
For this example on the "High Risk Register" Tab column H is "PEP". I wish the filter to show only results for "Yes". The information to this comes from the "Queries" tab via formula.
Please let me know any other details required.
Many thanks,
Feb 18 2020 12:27 AM
For that first place the following code on a Standard Module like Module1...
Sub filter_on_department(ws As Worksheet)
ws.Range("A1").CurrentRegion.AutoFilter field:=8, Criteria1:="Yes"
End Sub
Then place the following code on Queries Sheet Module (not on standard module)...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim wsToFilter As Worksheet
Set wsToFilter = Worksheets("High Risk Register")
If Target.Column = 9 And Target.Row > 1 Then
filter_on_department wsToFilter
End If
End Sub
So now as per the above codes, once you select either Yes or No in column I on Queries Sheet, the data on High Risk Register would be automatically filtered.
Feb 18 2020 02:37 PM
Thank you kindly again@Subodh_Tiwari_sktneer
This works exactly as desired, thank you for continuing to be a real asset to this comunity.
Thanks,
Jan 07 2020 09:12 PM
SolutionYou need only one line of code to filter the column H which is like this...
Sub filter_on_department()
Range("A1").CurrentRegion.AutoFilter field:=8, Criteria1:="Yes"
End Sub
Btw, you have placed the event codes on Standard Modules which is not the right place for them.
You should place the Worksheet_Change event code on the Sheet Module and Workbook_Open event code on ThisWorkbook Module.