Unknown Sheets in WB

[Brian.Crawford]

Excel 2007. Over time (and many [100's] Excel "Is not responding" anymore "the white screen of death") I seemed to have accumulated a few extra WS in my WB. These sheets are not viewable (cannot be UnHidden) and do not have there VeryHidden property set.

What is odd is that these "sheets" do not have the "sheet" icon in project explorer, instead they have the workbook icon.

Also, they are called "sheets" (10 & 16) in project explorer but when clicked on their "type" (ie name) in their properties window is "thisWorkbook" along with the real "thisWorkbook" (that looks and is normal, with code).

There is no code associated with these "things".
Suggestions on their source and how to get rid of them (can't remove or delete them)?

thanks
Brian

 

[mikerickson]

Code like

ActiveSheet.Copy

will create a new Workbook with a name like Sheet1. That may be the source of your problem.

 

[Brian.Crawford]

That is very possibly what happened...

But how do I get rid of it now.
I can't see them in "Excel" (on sheet tabs) and project explorer won't let me do anything with them either.

 

[doofusboy]

Can you delete them via code in the Immediate Window?

 

[mole999]

maybe ASAP utilities ?

 

[Brian.Crawford]

I tried "sheets(...).delete" in the immediate window but no luck.
These sheets do not a a name (other than the sheet number [ex Sheet16]) so I clicked on one and tried to rename it and... it ended up renaming the real "thisWorkBook" (tried it twice to be sure).

That is a bit scary?
thoughts anyone

Brian

 

[doofusboy]

Do they show up if you do something like this?

Dim ws As Worksheet

For Each ws In Worksheets
Msgbox ws.Name
Next ws

 

[Brian.Crawford]

no, they do not

they have sheet numbers but they act/look like like WBs

 

[mikerickson]

It sounds like they are not worksheet, but workbooks.
Try this, close the workbooks that you like and want.
Open a new workbook, put this macro in that workbook and run it

Sub ListWorkbooks()
    Dim i as Long
    For i = 1 to Workbooks.Count
        With Workbooks(i)
            ActiveSheet.Cells(i, 1).Value = .Name
            ActiveSheet.Cells(i, 2).Value = .Path
            ActiveSheet.Cells(i, 3).Value = "contains " & .Sheets.Count & " sheets"
        End With
    Next i
End Sub

Then look at the sheet in the new workbook to see what is what. The path will let you know where to do to delete the files.

 

[Brian.Crawford]

Not sure I understand, ..these "things" are all in a single project (under one project/WB name an ".xlsm") so not sure how differntiate/select the ones I "like". I just now opened another WB and I see it has some of the same types of things in it? It to has been party to some Excel crashes.