New HIDE/UN-HIDE Sheet Problem

[Superdaveyk]

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

 

[Menumorut]

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]

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]

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]

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]

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.

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]

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

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]sheet1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]sheet3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]sheet5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]sheet15[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]sheet67[/TD]
[/TR]
</tbody>[/TABLE]

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]

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