Showing Worksheets Problem

[Pacman52]

Hi all I'm completely lost with why I can't show some worksheets in a macro enabled workbook.

The right click tab options to show or hide worksheets is greyed out, using any sort VBA like Visable, xlVisable, -1 etc throws up the error object doesn't support this method nothing I'm doing seems to allow me to show the worksheets even though they are showing in the VBA project window tree as existing.

As far as I am aware I've blocked out any code references that I had in the modules that referred to the visibility of a sheet, I've removed both the worksheets and workbook protection and re-named the workbook using Save As (even though I know is a bit pointless)

Can anyone offer any solutions or things to check that I'm just not doing as its driving me nuts why something so simple is becoming so difficult and time consuming to the point I'm on the verge of just re-doing the whole workbook again from scratch (its not even an important workbook just something that was meant to make my life a bit easier)

Thanks for any replies offered.

Paul

 

[Alex Blakenburg]

This is from another post here:

VBA Object Projects shows sheets that do not exist

Hi all! Something weird has happened to my VBA Objects.. Currently my VBA objects look partly like this: However, in my worksheet, i do not have this many sheets! The only sheets that exist are those with the symbols of Sheet 32 and 33: . I have the projects for the first 30 or so sheets...

www.mrexcel.com

 

[Pacman52]

Thanks again Alex - I never knew about that but lesson learned. I've resigned myself to rebuilding the workbook from scratch as to be honest it isn't even a complex project and would be quicker to rebuild than troubleshoot the current one.

I'd love to be able to access the Data sheet if at all possible though but its not the end of the world if I can't

Thanks Alex and everyone else for the replies.

Paul

 

[Alex Blakenburg]

Are you able to share your workbook for us to have a look ?
(via dropbox, googledrive....)

 

[Pacman52]

Are you able to share your workbook for us to have a look ?
(via dropbox, googledrive....)

Sadly Alex I can't as it contains unrestricted sensitive information.

On the problematic WB I have got a listbox that does show the actual data when its opened so on that basis I'm sort of 'confident' that the data worksheet does exist though which is a small positive.

I plant to try a few more things to get the data copied but I looked at my backup and thankfully it's only a week old so there won't be that much data missing from it if or when I rebuild.

 

[Alex Blakenburg]

I suspect you want the formulas but if you only want the data you could try:
• Using Power query from another workbook.
• From another workbook try using "=TheOtherWorkbook!A1 and copy it to all a decent range of cells.

 

[Pacman52]

Hi Alex Just to let you know that I've manged to actually get the most current data copied over to a new Workbook. One of the sheets was for searching data so just as a test I cleared the active search filter and used a wildcard and the results showed everything so from that point on it was a simple copy and paste the values into a new workbook. I've still got to sort to format and code the new WB but hopefully that won't take long to sort out.

Thanks again for your help and advice on this.

 

[Alex Blakenburg]

You're welcome. Glad to hear you were at least able to recover the data.
It would be interesting to know if the filter worked because it had the data cached or whether it is actually reading the sheet.
If you loop through the sheets without using the sheet name can it find them ie

Sub test()
    Dim ws As Worksheet
    For Each ws In Worksheets
        Debug.Print ws.Index, ws.Name
    Next ws
End Sub

 

[Pacman52]

You're welcome. Glad to hear you were at least able to recover the data.
It would be interesting to know if the filter worked because it had the data cached or whether it is actually reading the sheet.
If you loop through the sheets without using the sheet name can it find them ie

Sub test()
    Dim ws As Worksheet
    For Each ws In Worksheets
        Debug.Print ws.Index, ws.Name
    Next ws
End Sub

Hi Alex the filter was referencing the actual sheet thankfully. I ran the test Sub and it did show all the sheets so they are in the WB - Perhaps Microsoft have released a new option to very very very hide a sheet and forgot to tell us users Anyway thankfully I'm nearly back up and running now although I did think of a few additions to the WB that would be useful but will post that in another thread. Thanks again for all your help.

 

[Alex Blakenburg]

You're welcome and thanks for the additional info. It will give me some ideas for other things to try in the future.