WorkBook Open to show all Autofiltered rows

[Dave T]

Hello All,

I use the following macro in a workbook and would like to modify it so that each time the workbook is opened each of the worksheets has the AutoFilter criteria removed.
I would like the Filter criteria to be reset to 'All' but still retain the AutoFilter dropdown arrows.

Private Sub Workbook_Open()
    Dim Sh As Worksheet
    Application.ScreenUpdating = False
    For Each Sh In ThisWorkbook.Worksheets
    Sh.Select
    Sh.Protect userinterfaceonly:=True
    Sh.EnableSelection = xlUnlockedCells
    Sh.EnableAutoFilter = True
Next
    Sheets(1).Select
    Application.ScreenUpdating = True
End Sub
[Code]

Thanks in advance

Regards,
Dave T

 

[Sandeep Warrier]

you could try something like

For Each Sheet in Workbook
Sheet.Select
ActiveSheet.ShowAll
Next Sheet

 

[Dave T]

Hello sandeep.warrier,

Thanks for the reply

I tried to insert what you havew suggested but when the workbook opens I get an error message that says:

Run-time error '438':
Object doesn't support this property or method

I am using Excel 2003 so I don't know iif this has anything to do with the problem.

Regards,
Dave T

 

[loserkido07]

Can you help me with this problem?
Principle $140,000
Rate 7.25%
Years 15
Payement 1,278.01

The answer to the PV function is 134,638.06

 

[Sandeep Warrier]

I have tested this code and it works on my PC. I also use Excel 2003 so you should not have any problems..

Sub test()
 
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
    
        ws.Select
        ws.ShowAllData
    
    Next ws
 
End Sub

 

[Dave T]

Hello sandeep.warrier,

Thanks again for the reply

I tried what you suggested and even renamed all instances of Sh.Select, etc. to ws.Select and I now get an error message that now says:

Run-time error '1004':
Method 'ShaowAllData' of object '_Worksheet' failed

Could you please add/insert your suggested code to my original macro so I can see where it should be included and help me understand why i does not work for me.

Thanks again.

Dave T

 

[Sandeep Warrier]

I tested this too and it worked fine.

Please note that you need to paste this in the module for ThisWorkbook and not in any other module.

Private Sub Workbook_Open()
    Dim Sh As Worksheet
    
    Application.ScreenUpdating = False
    
    For Each Sh In ThisWorkbook.Worksheets
        
        Sh.Select
        Sh.ShowAllData
        Sh.EnableSelection = xlUnlockedCells
        Sh.EnableAutoFilter = True
        Sh.Protect userinterfaceonly:=True
        
    Next
    
    Sheets(1).Select
    
    Application.ScreenUpdating = True
End Sub

Regards,
Sandeep.

 

[Tom Urtis]

ShowAllData will error if all data is already showing.

Use

On error resume next
sh.showalldata
err.clear