Showing Worksheets Problem

Pacman52

Active Member
Joined
Jan 29, 2009
Messages
420
Office Version
  1. 365
Platform
  1. Windows
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 :ROFLMAO: (its not even an important workbook just something that was meant to make my life a bit easier)

Thanks for any replies offered.

Paul
 
This is from another post here:

1721643111709.png
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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

VBA Code:
Sub test()
    Dim ws As Worksheet
    For Each ws In Worksheets
        Debug.Print ws.Index, ws.Name
    Next ws
End Sub
 
Upvote 0
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

VBA Code:
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 :ROFLMAO: 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.
 
Upvote 0
You're welcome and thanks for the additional info. It will give me some ideas for other things to try in the future.
 
Upvote 0

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top