Delete method of Worksheet class failed

[gtonkin]

Had a template that I needed to run TM1 Print Report on, but kept failing with "Print Report-Delete method of Worksheet class failed" - turned out to be a sheet that was flagged as Visible:=2-xlSheetVeryHidden.

Changed to visible and deleted in my case-all fine now.
Seemed fine if set to 0-xlSheetHidden but Print Report does not like xlSheetVeryHidden.

[ngphong]

Thank you so much for this postings. It's very helpful. It worked for me when I changed the Visible to "0-xlSheetHidden" on the "Cognos_Office_Connection_Cache" which was not visible. It only shows on the VBA window of Developer menu.

[Wim Gielis]

Here is a VBA solution. I give it to my customers from a button in the Excel QAT (Quick Access Toolbar). Then it's right at their fingertips.

Code: Select all

Sub Delete_hidden_sheet()

	'Wim Gielis
	'October 2022

	'delete a specific worksheet that would otherwise make the TM1 Report Manager (TM1 Perspectives) choke and raise an error

    Dim ws As Worksheet

    'continue without stopping when errors are encountered
    On Error Resume Next
    
    Set ws = ActiveWorkbook.Worksheets("Cognos_Office_Connection_Cache")
    
    If Not ws Is Nothing Then
    
        'make the sheet visible
        ws.Visible = xlSheetVisible

        'delete the sheet without alerts
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    
    End If

    On Error GoTo 0

End Sub

[Wim Gielis]

Or, if you prefer a more condensed notation:

Code: Select all

Sub Delete_hidden_sheet()

    On Error Resume Next
    With ActiveWorkbook.Worksheets("Cognos_Office_Connection_Cache")
        .Visible = -1
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
    On Error GoTo 0

End Sub