New HIDE/UN-HIDE Sheet Problem

Superdaveyk

New Member
Joined
Jun 19, 2015
Messages
3
Good Morning all. Hope you are all well.

Hopefully someone on here can help me with an issue i'm having in excel and it's this:-

I have a workbook with around 150 sheets in. this workbook is a generic workbook that is used for many projects . . . however . . . not all sheets need to be viewed and subsequently printed off. I would like to hide all sheets by default in this workbook and save it.
When i work on a new project I generate a report from the other software I use. I would like to import the sheet names from this report paste them into column A on a separate sheet in my generic workbook and the VB code will UN-hide all sheets named in that column.

I'm not being lazy, I spent most of yesterday and a large chunk of last night tryign to get this working but to no avail. . . . . Please help a very tired Dave

Thanks

Dave
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Menumorut

Board Regular
Joined
Sep 26, 2013
Messages
51
Hello Dave,

Can't help you with VB but I can with hiding the sheets. You can right click one sheet, select all (bottom), ctrl+click one sheet to deselect, right click one of the selected and hide all.

For UN-hiding multiple at once this will not work. I google-ed it and apparently you can only do one at a time.

Cheers,
Emil
 

Superdaveyk

New Member
Joined
Jun 19, 2015
Messages
3
Hi Emil, and thanks for the reply.

I am quite familiar with Hiding/un-hiding sheets in the conventional way, but I'm after something more functional than your solution. But really thanks anyway. Anyone have the Vba solution to un-hide sheets based on data contained in a column rather than a single cell?

Thanks in advance for your continued support

Dave
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What do you actually have in the cells in the range? just a column letter, a column number, some other text which includes a column letter or number (this option will probably be a pain and we will need to see examples).

What is the range of the cells in what column?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Don't know why I stated columns in the last post. It Should read...

What do you actually have in the cells in the range? just a Sheetname, a Sheet Index, some other text which includes the sheetname or Index number (this option will probably be a pain and we will need to see examples).

What is the range of the cells in what column?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
I'll leave you with this as I am just going out. It assumes you do have the actual sheetname in the cells (this needs to be exact).

Things you might need to change...
1) It is setup that your range is on Sheet7, obviously change it to suit
2) It is setup that your range is A2 to the last cell in column A with data, again change it to suit.

Code:
Sub UnhideSheets()
    Dim rCell As Range, SheetArray() As String, Cnt As Integer
    Application.ScreenUpdating = False
    Cnt = 0
    With Sheets("Sheet7") 'CHANGE TO SUIT
        For Each rCell In .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
            If rCell.Value <> "" Then
                ReDim Preserve SheetArray(Cnt)
                SheetArray(Cnt) = rCell
                Sheets(SheetArray(Cnt)).Visible = True
                Cnt = Cnt + 1
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub
 

Superdaveyk

New Member
Joined
Jun 19, 2015
Messages
3
Hi Mark

Thanks for the code. It didn't work but it's the closest I've seen so far. The cell range will be A1 to A40. Each cell will contain the sheet name that I wish to un-hide.

I've altered the code as follows:

Sub UnhideSheets()
Dim rCell As Range, SheetArray() As String, Cnt As Integer
Application.ScreenUpdating = False
Cnt = 0
With Sheets("CList") 'CHANGE TO SUIT
For Each rCell In .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
If rCell.Value <> "" Then
ReDim Preserve SheetArray(Cnt)
SheetArray(Cnt) = rCell
Sheets(SheetArray(Cnt)).Visible = False
Cnt = Cnt + 1
End If
Next
End With
Application.ScreenUpdating = True
End Sub

My cell values will look as follows

A
1
sheet1
2sheet3
3sheet5
4sheet15
5sheet67

<tbody>
</tbody>

The sheet names pasted into this column I would like to unhide, the rest to remain hidden.

Thanks again for the reply, it's looking promising.

Kind regards

Dave
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
If it is the actual sheetname in the cells (the exact name on the sheet tab) then the code should work with you just changing the A2 to A1.
If it doesn't then for me to help any further I would need to see the actual workbook (sensitive data amended) as it made the sheets unhidden each time with my data, to do this post a copy of your workbook on a free hosting site like www.Box.com, mark it for sharing and post the link it provides in the thread.

Please note that I am answering on my phone and won't be in for hours.

I have just noticed that you have changed Visible = True to Visible = False...
Why! you want to unhide them not hide them. Change it back
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

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
Top