Hiding and Unhiding Tabs

[rhombus4]

I have 50 sheets. On each sheet is a box to hide sheets 1-10 and one to unhide sheets 1-10.

Once unhidden when I view the sheets 1-10 to view stats, the hide macro is on each of those sheets.

What I need is if I am viewing sheet 25 then unhide and view one of the first 10 sheets, If I then hide the first 10 sheets I want it to go to the sheet i was viewing i.e. sheet 25. Is this possible

 

[JoeMo]

Here's an example you can adapt to fit. The idea is to store the sheet index of the sheet from which the unhide is triggered. That index is stored in some sheet/range you specify. Then when the sheet hide module is run, it gets the index and activates the sheet with that index.

Sub UnhideSht()  'Run from a sheet that's visible and will stay visible
Dim Idx As Long
Idx = ActiveSheet.Index
Sheets("Sheet7").Range("B1") = Idx 'Store the index
Sheets("Sheet3").Visible = True
End Sub

Sub hideSht()  'Run from the sheet to be hidden
Dim Idx As Long
ActiveSheet.Visible = False
Idx = Sheets("Sheet7").Range("B1").Value
Sheets(Idx).Activate
End Sub

 

[rhombus4]

Here's an example you can adapt to fit. The idea is to store the sheet index of the sheet from which the unhide is triggered. That index is stored in some sheet/range you specify. Then when the sheet hide module is run, it gets the index and activates the sheet with that index.

Sub UnhideSht()  'Run from a sheet that's visible and will stay visible
Dim Idx As Long
Idx = ActiveSheet.Index
Sheets("Sheet7").Range("B1") = Idx 'Store the index
Sheets("Sheet3").Visible = True
End Sub

Sub hideSht()  'Run from the sheet to be hidden
Dim Idx As Long
ActiveSheet.Visible = False
Idx = Sheets("Sheet7").Range("B1").Value
Sheets(Idx).Activate
End Sub

Not sure where to put the above in my two macros, I have a Hide and Unhide one. Do I need to put one of the above in the hide and the other in the unhide


Sub Hide()
Application.ScreenUpdating = False

Dim sh As Worksheet
For Each sh In Sheets(Array("sheet 1", "sheet 2", "sheet 3", "sheet 4", "sheet 5", "sheet 6", "sheet 7", "sheet 8", "sheet 9", "sheet 10"))
If sh.Visible = xlSheetVisible Then sh.Visible = xlSheetHidden
Next sh
Application.ScreenUpdating = True
End Sub

Sub UnhideSheets()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Sheets
sh.Visible = xlSheetVisible
Next

Sheets("sheet 1").Select
Application.ScreenUpdating = True
End Sub

 

[JoeMo]

Just follow the guidance in the remarks next to the sub names in my post. One is for hide the other for unhide.

 

[rhombus4]

doesnt seem to work,

eg when I unhide all sheets which unhides 1-10 then I i go to sheet 1 and run the hidesht macro it only hides sheet1

When I unhide It unhides 1-10 when I go to sheet 1 then click on a macro I need it to hide 1-10 then go to sheet I ran the unhide all sheets macro

 

[JoeMo]

You missed the point, don't use the code I posted directly. It was just a single sheet example. Use your code, but include in both your hide and unhide macros all the lines from my code that have Idx in them. Also, change the Sheets("Sheet7") reference to some sheet you do not hide and change the range from Range("B1") to a cell that's out of your way and not likely to be overwritten by any user.

 

[rhombus4]

You missed the point, don't use the code I posted directly. It was just a single sheet example. Use your code, but include in both your hide and unhide macros all the lines from my code that have Idx in them. Also, change the Sheets("Sheet7") reference to some sheet you do not hide and change the range from Range("B1") to a cell that's out of your way and not likely to be overwritten by any user.

but where does the idx code. before or after.

i.e. My hide code is

Sub Hide()
Application.ScreenUpdating = False

Dim sh As Worksheet

For Each sh In Sheets(Array("sheet 1", "sheet 2", "sheet 3", "sheet 4", "sheet 5", "sheet 6", "sheet 7", "sheet 8", "sheet 9", "sheet 10"))
If sh.Visible = xlSheetVisible Then sh.Visible = xlSheetHidden
Next sh

Application.ScreenUpdating = True
End Sub

You posted:
Sub hideSht() 'Run from the sheet to be hidden
Dim Idx As Long
ActiveSheet.Visible = False
Idx = Sheets("Sheet7").Range("B1").Value
Sheets(Idx).Activate
End Sub

So if I only add the bits with idx, should it be

Dim Idx As Long
Idx = Sheets("Sheet1").Range("A1").Value
Sheets(Idx).Activate

added to the end will become

Sub Hide()
Application.ScreenUpdating = False

Dim sh As Worksheet

For Each sh In Sheets(Array("sheet 1", "sheet 2", "sheet 3", "sheet 4", "sheet 5", "sheet 6", "sheet 7", "sheet 8", "sheet 9", "sheet 10"))
If sh.Visible = xlSheetVisible Then sh.Visible = xlSheetHidden
Next sh
Dim Idx As Long
Idx = Sheets("Sheet1").Range("A1").Value
Sheets(Idx).Activate
Application.ScreenUpdating = True
End Sub

 

[JoeMo]

Added to the end is fine. Don't forget to include the Idx lines in your Unhide module too. Run the unhide module first so you get the correct value of Idx.

 

[rhombus4]

Added to the end is fine. Don't forget to include the Idx lines in your Unhide module too. Run the unhide module first so you get the correct value of Idx.

I entered it at the end

Sub Hide()
Application.ScreenUpdating = False
Dim sh As Worksheet
For Each sh In Sheets(Array("sheet 1", "sheet 2", "sheet 3", "sheet 4", "sheet 5", "sheet 6", "sheet 7", "sheet 8", "sheet 9", "sheet 10"))
If sh.Visible = xlSheetVisible Then sh.Visible = xlSheetHidden
Next sh
Dim Idx As Long
Idx = Sheets("Sheet 1").Range("A1").Value
Sheets(Idx).Activate
Application.ScreenUpdating = True
End Sub

but shows run time error 9 and when I click on debug it highlights

Sheets(Idx).Activate

 

[JoeMo]

Is the sheet where you stored Idx called Sheet 1 or Sheet1? If Sheet 1, look at cell A1. Is there a value in it? If so, what's the value? If not, did you run the unhide macro first?