Macros - Filters

[Pinda]

I've have a tables of targets with the director
responsible for them against each target (1 director per
target), with an autofilter for the table.

On a separate sheet, I've set up macros linked to buttons
labelled with director's names, so when a director clicks
on his/her own name, it goes to the worksheet of targets
and filters the table to show the targets of that director
only. Here's the code:-


Sub CheSha()
'
' CheSha Macro
' Macro recorded 29/09/2003 by Bhupinder Rayat
'

'
Sheets("LDP Template").Select
ActiveSheet.ShowAllData
Selection.AutoFilter Field:=7, Criteria1:="Chetan Shah"
End Sub

I've recorded the macro to show all data first and then
get the required name. The trouble is when the table is
showing all data already, the macro fails because the show
all data command is not available.

Is it possible to insert an if statement within the above
code to say that if all data is showing
then "ActiveSheet.ShowAllData" does not need to be
performed.

Hope I've explained it sufficiently. Can anyone help?

Thanks in advance.

Pinda

 

[Pinda]

Could anyone please help me with this? Any variation of
the code would be ok too, I just dont want the macro to
fall down.

Thanks

 

[Bernie Deitrick]

Pinda,

Use

ActiveSheet.AutoFilterMode = False

instead of

ActiveSheet.ShowAllData

HTH,
Bernie
Soon-to-be-former Excel MVP

 

[Pinda]

Thank you soo much mate, works like a treat!!

Pinda.

 

[LD]

This is the best I could come up with.
Turn the autofilter off and on again.


Sub CheSha()
Application.ScreenUpdating = False
Sheets("LDP Template").Select
If Sheets("P&P Rating (Ctr)").AutoFilterMode = False Then
Selection.AutoFilter
End If
Selection.AutoFilter Field:=7, Criteria1:="Chetan Shah"
Application.ScreenUpdating = True
End Sub