0

I have a spreadsheet and it links to another workbook. The issue is when the filter is on it does not change as new data is added; you have to refilter the data.

enter image description here (Click image to enlarge)

I know there is a way to have it autofilter as data appears using a Macro. I have been trying to figure the Macro out for 5 hours now and can not get it right. The closest I've gotten is I set a filter and then when new data appears, it erases the filter and adds all data together. I need the filter to stay!

Private Sub Worksheet_Change(ByVal Target As Range)

    If Me.FilterMode = True Then
        With Application
           .EnableEvents = False
           .ScreenUpdating = False
        End With

        With ActiveWorkbook
            .CustomViews.Add ViewName:="B2", RowColSettings:=True
          Me.AutoFilterMode = False
            .CustomViews("B2").Show
            .CustomViews("B2").Delete
        End With


         With Application
           .EnableEvents = True
           .ScreenUpdating = True
        End With
    End If

End Sub
5
  • please post the code you have built so far and then we can help you edit it to get it working... Nov 26, 2015 at 2:42
  • Crap I apologize its not putting it in neatly how it is actually... Data is coming in how I want I just can not get it to sort by "Focus Factory" like I want... All the data just compiles there from west east and north even with a filter on specifying the north
    – Mitch
    Nov 26, 2015 at 2:55
  • edit your post and put the code there, not in a comment.
    – teylyn
    Nov 26, 2015 at 2:59
  • just entered image of spread sheet to
    – Mitch
    Nov 26, 2015 at 3:02
  • I appreciate you guys and any help you can provide I would like to start my holiday but need to figure this out first!
    – Mitch
    Nov 26, 2015 at 3:04

1 Answer 1

0

Essentially all you need to is "refresh" the filter by telling Excel to filter the same conditions on the new range of data.

Note that the use of UsedRange implies that all data is in contiguous rows and columns. Also, adjust the AutoFilter arguments as needed.

Since your data is linked via formulas, the Worksheet_Change will never fire when formulas are updated. You'll need to use the Worksheet_Calculate event, but be mindful this will fire everytime the worksheet calculates, which may or may not be burdensome.

I also changed your conditional If to say IF Me.AutoFilterMode = True since the FilterMode will not be true if nothing is actually filtered, even if the filters are on.

Private Sub Worksheet_Calculate()

    If Me.AutoFilterMode = True Then

        With Application
           .EnableEvents = False
           .ScreenUpdating = False
        End With

        Me.UsedRange.AutoFilter 2, "North" 'filters column B for myFilter value change as needed

         With Application
           .EnableEvents = True
           .ScreenUpdating = True
        End With

    End If

End Sub
10
  • Just added the code to my sheet and changed the "myFilter" to "North" because I want to filter it for that... Though as it pulls data from my other workbook its not filtering unless I go click the filter button still...
    – Mitch
    Nov 26, 2015 at 3:17
  • Sorry if it feels like your having to spoon feed me..
    – Mitch
    Nov 26, 2015 at 3:17
  • how does it "pull data from my other workbook"? by code? Nov 26, 2015 at 3:24
  • Its attached to another workbook and copies whatever was put in that workbook so =IF('[Daily Data Entry.xlsm]1st'!$I$8="","",'[Daily Data Entry.xlsm]1st'!$I$8) for example... So whenever data is entered there it pops up in this one and when that happens I need it to be filtered out by north in column b
    – Mitch
    Nov 26, 2015 at 3:28
  • ah, okay. that is why it's not working, because the Worksheet_Change event does not fire when formulas are updated You have to use Worksheet_Calculate - which can be tricky, because the code will fire everytime the worksheet is calculated. Editing my post now @Mitch. Nov 26, 2015 at 3:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .