VBA to remember current visible sheets and active cells, then unhide all hidden sheets. #2 to re-hide and set back to previous active cells

fruehling

New Member
Joined
Feb 5, 2009
Messages
45
Hi all,

Would love help on this. I've found lots of code online that come close to this, but I can't quite tweak it to be as simple as this should be.

I have a file with about 5-6 visible sheets and 9-10 hidden sheets. I would like to set up 2 macros.

#1. to take a look at, and remember how it is currently set up (which tabs are visible/hidden and which cells are the active cells on each), then to unhide all sheets. I was thinking an easy way might be to create a new sheet to temporarily store this info.

#2. to put everything back to how it was before the sheets were unhidden.

Can anyone help?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

fruehling

New Member
Joined
Feb 5, 2009
Messages
45
Actually, just realized that I could even do away with the active cell part, as we can just make cell A1 the activecell on all of them.

So basically, I just need a way to record what sheets are currently hidden/visible, then use that list to re-hide the proper sheets.
 

fruehling

New Member
Joined
Feb 5, 2009
Messages
45
So, I am using the following code to record the sheets and their visibility status on a "setup" sheet:

Code:
Sub SheetNames()
    ThisWorkbook.Sheets("setup").Activate
    For i = 1 To Sheets.Count
        Cells(i, 1) = Sheets(i).Name
        Cells(i, 2) = Sheets(i).Visible
    Next i
    
End Sub

So now that I've recorded the status of each sheet, I'm going to want to write an If to hide a sheet if the name in column A has a 0 in column B. Or, I could set all the sheets visibility status to what the sheet name corresponds to in B. Any help on that piece? I'll post back here if I work it out first.
 

fruehling

New Member
Joined
Feb 5, 2009
Messages
45
I've refined that a little (in a very hamhanded way. I'll refine it later, as this is mostly from recording), in order to just display the hidden sheets. I think this will make the next ste easier, to only hide sheets that are listed in Column A

Code:
Sub SheetNames()
    ThisWorkbook.Sheets("setup").Activate
    For i = 1 To Sheets.Count
        Cells(i, 1) = Sheets(i).Name
        Cells(i, 2) = Sheets(i).Visible
    Next i
    
    Range("A1:B1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Sheet"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "visibility"
    Range("A1:B1").Select
    Selection.AutoFilter
        ActiveWorkbook.Worksheets("Setup").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Setup").AutoFilter.Sort.SortFields.Add key:=Range( _
        "B1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Setup").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:="-1"
    Columns("A:B").Select
    Selection.ClearContents
    Range("A1").Select
        Range("A1:B1").Select
    Selection.Delete Shift:=xlUp
    
End Sub
 
L

Legacy 320475

Guest

ADVERTISEMENT

Building off what you have so far, how about this edit?
Code:
Sub SheetNames()
    Dim Sheet1 As Worksheet
    
    For Each Sheet1 In Worksheets
        If Sheet1.Visible = False Then
            ThisWorkbook.Sheets("Setup").Cells(Sheet1.Index, 1) = Sheet1.Name
        End If
    Next Sheet1
End Sub

Followed by:

Code:
Sub Rehide()
    Dim Cell1 As Range
    Dim Range1 As Range
    
    Set Range1 = Sheets("Setup").Range(Sheets("Setup").Cells(1,1),Sheets("Setup").Cells(1,Sheets.Count))


    For Each Cell1 In Range1
        Sheets(Cell1.Value2).Visible = False
    Next Sheet1
End Sub

I assume here that it might benefit you to be able to interact with the sheets while they are unhidden, thus splitting it into two macros and printing the names to the Setup sheet as you tried to do.
 

fruehling

New Member
Joined
Feb 5, 2009
Messages
45
Thanks for that! Helps a lot. We're close, but the Rehide code is throwing a :
Compile error: Invalid Next control variaable reference.
 
L

Legacy 320475

Guest

ADVERTISEMENT

Thanks for that! Helps a lot. We're close, but the Rehide code is throwing a :
Compile error: Invalid Next control variaable reference.

Oops oops! In Rehide(), change "Next Sheet1" to "Next Cell1"
 
L

Legacy 320475

Guest
:)

Even better! But now I'm getting an
Run time error 9:
Subscript out of range
on this part:

Code:
        Sheets(Cell1.Value2).Visible = False

very close

Sorry for not testing all this the first time, I was at work. Just got off and tested it myself, I made changes to both macros. Here are the revised ones:

Code:
    Sub SheetNames()
    
    Dim Sheet1 As Worksheet
    
    For Each Sheet1 In Worksheets
        If Sheet1.Visible = False Then
            ThisWorkbook.Sheets("Setup").Cells(Sheet1.Index, 1) = Sheet1.Name
            Sheet1.Visible = True
        End If
    Next Sheet1
    
    End Sub

Code:
Sub Rehide()    
    Dim Cell1 As Range
    Dim Range1 As Range
    
    Set Range1 = Sheets("Setup").Range(Sheets("Setup").Cells(1, 1), Sheets("Setup").Cells(Sheets.Count, 1))

    For Each Cell1 In Range1
        If IsEmpty(Cell1) = False Then
            Sheets(Cell1.Value2).Visible = xlHidden
            Cell1.ClearContents
        End If
    Next Cell1

End Sub
 

fruehling

New Member
Joined
Feb 5, 2009
Messages
45
No need to apologize at all. I'm greatful for your help. The edit worked great! I have some protection, so I had to add that in and account for that.

One last thing I forgot to mention, and I really hope it's easy to take care of. There are some hidden rows/columns on the sheets that i'm unhiding in the first step with this:

Code:
    Sheet1.Rows.EntireRow.Hidden = False
    Sheet1.Columns.EntireColumn.Hidden = False

However, upon rehiding the sheets, I need to also rehide the rows and columns that were hidden when the file was opened. Any clever ideas how to handle that? Would it be best to record that on the setup tab as well somehow?
 

Forum statistics

Threads
1,136,326
Messages
5,675,122
Members
419,551
Latest member
thangxpm

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