Hide blank sheets that do not have an object embedded

[Bagsy]

I wonder if anybody can tell me if this is possible
I have a workbook with a few blank sheets; these are used to embed pdf documents if required. However I would like to is run some code to hide the empty sheets if there has not been a pdf document embedded within that sheet.
Below code will hide empty sheets but also hide those with an embedded document which I need to avoid.
Any help is much appreciated

Sub HideBlank()

Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If (ws.name <> "PSW") And (ws.name <> "Del COC") And (ws.name <> "Del COC") And (ws.name <> "Del Note") And (ws.name <> "COC") And (ws.name <> "L1 Insp Report (1)") And _
        (ws.name <> "L1 Insp Report (2)") And (ws.name <> "L1 Insp Report (3)") And (ws.name <> "L1 Insp Report (4)") And (ws.name <> "L1 Insp Report (5)") And (ws.name <> "L1 Insp Report (6)") Then
            If WorksheetFunction.CountA(ws.Cells) = 0 Then
                ws.Visible = xlSheetHidden
            End If
        End If
    Next
End Sub

 

[Domenic]

Try...

Sub HideBlank()

Dim ws As Worksheet


    For Each ws In ThisWorkbook.Worksheets
        If (ws.Name <> "PSW") And (ws.Name <> "Del COC") And (ws.Name <> "Del COC") And (ws.Name <> "Del Note") And (ws.Name <> "COC") And (ws.Name <> "L1 Insp Report (1)") And _
        (ws.Name <> "L1 Insp Report (2)") And (ws.Name <> "L1 Insp Report (3)") And (ws.Name <> "L1 Insp Report (4)") And (ws.Name <> "L1 Insp Report (5)") And (ws.Name <> "L1 Insp Report (6)") Then
            If WorksheetFunction.CountA(ws.Cells) = 0 Then
                If ws.Shapes.Count = 0 Then
                    ws.Visible = xlSheetHidden
                End If
            End If
        End If
    Next
End Sub

Note that the macro won't hide sheets that contain...

1) shapes, such as rectangles, circles, etc.

2) activeX objects, such as commnad buttons, labels, etc.

3) other types of embedded files, such as an Excel workbook, Word workbook, etc.

If you only want to hide sheets when there are no embedded PDF files, the macro will need to be amended. Post back if this is the case.

 

[Bagsy]

That is brillaint,Domenic. Thank you so much
Gary