Macro debugs when not filtering

[projectile]

I have a worksheet "Latest Rates" which uses autofilter and so I have setup a macro to reset filtering when the exel sheet is opened and works fine; however if the sheet is opened and reset is not applicable i.e. no filter criteria saved from last time it was used, the macro crashes. below is the code I entered

Private Sub Workbook_Open()
Sheets("Latest Rates").Select
With ThisWorkbook.Worksheets("Latest Rates")
If .AutoFilterMode Then
.ShowAllData
End If
End With
End Sub

please advise

 

[hiker95]

projectile,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. And, paste the copied code into VBAProject, Microsoft Excel Objects, ThisWorkbook (on the right pane) by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Workbook_Open()
Sheets("Latest Rates").Select
With ThisWorkbook.Worksheets("Latest Rates")
  If .FilterMode = True Then
    .ShowAllData
  End If
End With
End Sub

Save the workbook, close the workbook, then open the workbook.

 

[projectile]

Perfect - Thank you very much

 

[hiker95]

projectile,

Thanks for the feedback.

Glad I could help.

Come back anytime.