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

So what I'm trying to accomplish is;

Delete any sheet(s) with any titel that is not "PR11_P3".

In this this remaining sheet there is a table "PR11_P3_Tabell" which is by now always filtered or rather sorted somehow and this is what I'm trying to restore with 'ShowAllData'.

Obviously not going well for me so I ended 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

CodePudding user response：

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