Refresh Autofilter

[Guest]

I'm very new to marcos, so please be gentle....

I have a worksheet named Summary which uses Auto Filter (Non-Blanks). It's
important that none of the formulas get messed up on this page, so I've
protected it. Depending on data entered on other worksheets, rows on the
Summary sheet may or may not be blank. I would like Summary to re-do the auto
filter and remain protected everytime the workbook re-calculates. I tried
recording a marco and here's what I've got so far. All help is appreciated.
It says Active sheet, but Summary will hardly ever be the sheet I'm looking
at when the workbook calculates. I only need it to affect the worksheet
Summary.

Thanks,

Sub RefreshAutoFilter()
'
' RefreshAutoFilter Macro
' Macro recorded 10/9/2006 by sb1920alk
'

'
ActiveSheet.Unprotect
Selection.AutoFilter Field:=1, Criteria1:="<>"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[ducky]

'
' RefreshAutoFilter Macro
' Macro recorded 10/9/2006 by sb1920alk
'

'
ActiveSheet.Unprotect
Selection.AutoFilter Field:=1, Criteria1:="<>"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

looks like you are on the right track. store your code in a proc
called Workbook_SheetCalculate. doing that will trigger exectuion
every time there is a calculation

HTH

AR

 

[Guest]

Not quite sure what you mean

looks like you are on the right track. store your code in a proc
called Workbook_SheetCalculate. doing that will trigger exectuion
every time there is a calculation

HTH

AR

 

[Guest]

I've been messing with this and haven't been getting anywhere. I tried
running it from another worksheet, but it did the Auto Filter on that sheet
instead of the Summary sheet. I altered the code like this, but it didn't
help.

Sub RefreshAutoFilter()
'
' RefreshAutoFilter Macro
' Macro recorded 10/9/2006 by sb1920alk
'

'
Worksheets("Summary").Unprotect
Selection.AutoFilter Field:=1, Criteria1:="<>"
Worksheets("Summary").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

 

[Tom Ogilvy]

http://www.cpearson.com/excel/events.htm

for an overview of events. (Chip Pearson's site)