Show All Data VBA

[SDave]

Afternoon all,

I have the following code, which up until yesterday was working fine.

It now debugs on ShowAllData - Method "ShowAllData" of object worksheet failed (Runtime error 1004)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws As Worksheet
    Dim msg As String, _
    orgWs As String
    Dim Ctrl As Office.CommandBarControl
 
    Application.ScreenUpdating = False
 
    For Each ws In Worksheets
    If ws.FilterMode Then ws.ShowAllData
    ws.PageSetup.PrintArea = ""
    Next ws
 
    For Each Ctrl In Application.CommandBars.FindControls(ID:=21)
    Ctrl.Enabled = True
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=22)
    Ctrl.Enabled = True
    Next Ctrl
 
    Sheets("TM_Feed").Visible = True
    Sheets("TM_Feed").Select
    Range("A3:L1002").ClearContents
    ActiveWindow.SelectedSheets.Visible = False
 
    Sheets("Important_Information").Select
    Range("A1").Select
 
    Application.ScreenUpdating = True
End Sub

Any ideas?!

 

[sous2817]

This should work (albeit untested)

    For Each ws In Worksheets
    If ws.FilterMode Then 
         ws.unprotect Password:="HUBBA"
         ws.ShowAllData
         ws.PageSetup.PrintArea = ""
         ws.protect Password:="HUBBA"
    end if
    Next ws

 

[SDave]

Thanks Sous. Much appreciated.

 

[sous2817]

Glad I could help, good luck with the rest of your project!