Testing a sheet for a change since last accessed

[Breck]

I'm using the following code in an Excel spreadsheet that I keep track of
volleyball statistics for my daughters club team. I use the autofilter to
determine the number of rows that I want displayed on a sheet that is
determined usually prior to each tournament. I enter the number of rows that
I want in a cell on a different sheet in another part or the workbook. When
I access any sheet with this code there is a slight pause while the filter
is working. I would like to add some additional lines of code to test to see
if the number of rows has changed since the last time that the sheet was
accessed. The number that is entered on the other sheet doesn't change a lot
but it does change occasionally. Most of the time that it changes is when
I'm doing modification or testing. If either the number that I enter in has
not changed or the number of rows displayed on the sheet hasn't changed
since the last time I accessed the sheet I want to skip the line with the
AutoFilter code. This code is on multiple sheets so I suppose I could use an
entirely different approach and somehow activate all of the multiple sheets
that has the autofilter code on them but I'm usually only using a 2 or 3
different sheets during each tournament so I've decided to to use the
following code on a sheet by sheet basis. I hope that this makes sense. BTW
nothing is every entered on these sheets. They are used for summarizing data
that is entered on other sheets. Thanks in advance for any responses to my
question.

Private Sub worksheet_activate()

Application.ScreenUpdating = False

Range("AI1:AI262").AutoFilter Field:=1, Criteria1:="<>0"

Application.ScreenUpdating = True

End Sub

 

[RadarEye]

Hi Breck,

For each sheet with detail try something like this:

Public blnChanged As Boolean
Public intShowRows As Integer

Private Sub Worksheet_Activate()
If blnChanged Then
MsgBox "There are new rows"
Application.ScreenUpdating = False
Range("AI1:AI262").AutoFilter Field:=1, Criteria1:="<>" & CStr
(intShowRows)
Application.ScreenUpdating = True
blnChanged = False
Else
Application.ScreenUpdating = False
Range("AI1:AI262").AutoFilter Field:=1, Criteria1:="<>0"
Application.ScreenUpdating = True
End If
End Sub

On the main sheet try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
Sheet2.blnChanged = True
Sheet2.intShowRows = Target.Value
End If

If Target.Address = "$B$4" Then
Sheet3.blnChanged = True
Sheet3.intShowRows = Target.Value
End If

' Continued for each detail sheet
End Sub

HTH,

Wouter