Applying multiple filter to data using VBA

Hi there, hope all is well!

Is there a way I can apply more than one filter to a data table from cells on another sheet using VBA?

Picture1.jpg

So in the above example, if I enter grade1 in cell B1 and 6 in cell B2 on sheet 1, my table on sheet 2 would filter on grade 1 and 6.

Many Thanks in advance

Not everybody wants to construct your sheet - quicker answers come when you help us to help you.

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Apologies, I should have attached my workbook.

I found some code online which allows me to apply a filter on the data table on the same sheet but I can't work out how to apply multiple filters and from sheet 1

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Address = Range("E2").Address Then
       Range("A6:d25").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
   End If
End Sub

Please find attached example.

Many Thanks

Thanks, sorry if this is a silly question, do i still need to set a target.address ? So on sheet1, if I right click on the tab -> "View Code" and then...

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = Range("A2").Address Then
       Sheets("Sheet2").Range("A1").CurrentRegion.AdvancedFilterxlFilterInPlace , Sheets("Sheet1").Range("A1:B2")
   End If
End Sub

Thanks!!

I would use

If Not Intersect(Target, Range("A2:B2")) Is Nothing Then

That way it will trigger if you change either A2 or B2

Awesome, thank you that works now! The only thing I had to add was "CriteriaRange:=" at the criteria section. So its reads...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:C2")) Is Nothing Then
Sheets("Sheet2").Range("A9").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("Sheet1").Range("A1:C2")
End If
End Sub

Many Thanks for your help, much appreciated!!