What am I missing? A very simple Macro with basic command doesn't work

April 4, 2022

So what I’m trying to accomplish is;

If there is more then one sheet with titel PR11_P3 (so any amount of sheets with any titel) then the sheet or these sheets get deleted and then in this remaining sheet there is a table (PR11_P3_Tabell), and that table by now is always filtered or rather sorted somehow and this I want to restore with ShowAllData.

This is obviously not going well for me so I end up here asking for help!

    Sub DeleteSheetRestoreSort()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "PR11_P3" Then
        ws.Delete
        Then
            ActiveSheet.ShowAllData
    End If
    Next ws
   
End Sub

>Solution :

There is no guarantee that ActiveSheet is the sheet you want to clear. Use ws instead.

Sub DeleteSheetRestoreSort()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "PR11_P3" Then
            ws.Delete
        Else
            If ws.filtermode then
                ws.ShowAllData
            End If
        End If
    Next ws
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub