Easy Question I'm sure. Filter Results on each page open

[thiess22]

I'm thinking this is an easy one but I can't get it to work for me. I have one work sheet that unfilters and then filters results on a cell change. I have two other worksheets though where I want to accomplish the same thing for cells on their respective pages but instead of on a cell change I'd like it to run each time the worksheet is clicked on.

Here's the code I'm using for the page than works on cell change. That works great.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("b12")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

' Display a message when one of the designated cells has been
' changed.
' first remove filter
ActiveSheet.Range("A1:AE3200").AutoFilter Field:=7
' then apply it again
ActiveSheet.Range("A1:AE3200").AutoFilter Field:=7, Criteria1:="<>0"

End If
End Sub


I'm sure this is simple I just can't seem to find the answer and am not great with this stuff yet.

 

[johnnyL]

Private Sub Worksheet_Activate()
'    Code that you want to execute
End Sub

Put that in the sheet code and it will execute every time that sheet is selected.

 

[thiess22]

I tried that but I kept getting errors. I even tried just putting it at the beginning of this formula and I couldn't get it to run. Shouldnt this work?

Private Sub Worksheet_Activate()

ActiveSheet.Range("A1:AE3200").AutoFilter Field:=7

ActiveSheet.Range("A1:AE3200").AutoFilter Field:=7, Criteria1:="<>0"

End Sub

 

[johnnyL]

You want the same code to run on all sheets?

 

[thiess22]

No, I have one sheet I want it to run on when that sheet is clicked on. To filter out B10:E3000 for any lines in field 1 with the word "Hide"

Then I have a second sheet that I want it run as well when that sheet is clicked on that filters out a to be determined range.

Workbook has several other sheets as well that I don't filtered at all.

 

[johnnyL]

Ok. Give me a moment. In the mean time, delete the code from post #3 from the sheet module you placed it in.

 

[johnnyL]

Ok. In the 'ThisWorkbook' module paste the following code:

Private Sub Workbook_SheetActivate(ByVal SelectedSheet As Object)
'
    If SelectedSheet.Name = "SomeSheeetName" Then       ' <--- fix the sheetName
'       Put your code here
    End If
'
    If SelectedSheet.Name = "AnotherSheetName" Then     ' <--- fix the sheetName
'       Put your code here
    End If
End Sub

Then correct the sheetnames and insert the code that you want to run on each sheet when that particular sheetname is clicked on.

 

[thiess22]

Cant get it to work. Perhaps i'm making a dumb mistake when try to put that the code in for the filter?


Private Sub Workbook_SheetActivate(ByVal SelectedSheet As Object)

If SelectedSheet.Name = "JOB ESTIMATE" Then


ActiveSheet.Range("C11:E702").AutoFilter Field:=1

ActiveSheet.Range("C11:E702").AutoFilter Field:=1, Criteria1:="<>0"



End If

End Sub

 

[thiess22]

I tried filtering outlines with zero in that code vs "Hidden"

 

[johnnyL]

What does 'can't get it to work' mean? It does nothing? It errors? Please be specific about what it does/does not do.

I know almost nothing about filters, so I won't be of much help there.