Forum Discussion
Getting Table Filters to automatically update
Keith Farmery yes. The code needs to go in the sheet module where "Revised_Balance" is housed. So you could use this...
Set Table=ThisWorkBook.Worksheets("Journal").ListObjects("Journal") Set DataChangeRange =Me.Range("Revised_Balance")Works for me in my tests. If it's still not working let me know, perhaps you can upload your file.
Keith Farmery hello there!
You could use a worksheet change event on your block of data. In that worksheets code module (right-click the sheet name, select 'View Code'), paste this...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Table As ListObject
Dim DataChangeRange As Range
Set Table = ThisWorkbook.Worksheets("Sheet1").ListObjects(1) ' << Set to your Table of data to filter
Set DataChangeRange = Me.Range("A1:A100") ' << Set to where your block of data resides
If Not Intersect(Target, DataChangeRange) Is Nothing Then
Table.Range.AutoFilter Field:=1, Criteria1:="<>"
End If
End Sub
HTH
Zack
Thanks for this.
I've inserted the code, but it is not working.
I'm not sure what I've done wrong.
Can I please clarify:-
Set Table = ThisWorkbook.Worksheets("Sheet1").ListObjects(1) ' << Set to your Table of data to filter
Set DataChangeRange = Me.Range("A1:A100") ' << Set to where your block of data residesMy worksheet is called "Journal" and my Table is also called "Journal", the data that would trigger an update to my table is an area called "Revised_Balance"
So is the following correct?
Set Table=ThisWorkBook.Worksheets("Journal").ListObjects(1)
Set DataChangeRange =Me.Range("Revised_Balance")
Thanks for your help
Keith
Keith Farmery yes. The code needs to go in the sheet module where "Revised_Balance" is housed. So you could use this...
Set Table=ThisWorkBook.Worksheets("Journal").ListObjects("Journal") Set DataChangeRange =Me.Range("Revised_Balance")Works for me in my tests. If it's still not working let me know, perhaps you can upload your file.