VBA to unhide all sheets but return to active sheet

[colzre]

Hello Experts,

I have got below VBA coding which works fine and when I run it, all sheets unhide but for some reason then doesn´t stay in the active sheet I was prior it and goes to the last sheet of my workbook of all of them which just got unhidden.

Any chance to run the VBA and stays in the active sheet afterwards in same place as it was previously?

Sub Unhide_all()

Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        
        ws.Visible = xlSheetVisible
    
    Next ws


End Sub

Cheers,
John

 

[MARK858]

Do you have any other code in the workbook? asking because the Activesheet and Activecell remain the same for me when running the code

 

[colzre]

Do you have any other code in the workbook? asking because the Activesheet and Activecell remain the same for me when running the code

I have 2 other VBA created in the workbook however they are not part of this run and there are on separate modules.

See below these 2 if that helps for any possible solution. First is to extract a file and copy paste values, second is to hide the sheets.

Sub Estraz()


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'*****************set variables
    
Dim ws As Worksheet
Dim wkbk
Dim wkbk2
Set wkbk = ActiveWorkbook

'*****************Clearcontents



'*****************Extraction



     fname = Application.GetOpenFilename
    If fname <> False Then
    Workbooks.Open Filename:=fname
    Else: Exit Sub
    End If
 
error100:
MsgBox "               Ok! Now check cell T1"
 
Set wkbk2 = ActiveWorkbook

'*****************Copy and paste
    ActiveWindow.WindowState = xlMaximized
    
     
     Range("a2:o500").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    wkbk.Activate
    Range("a2").Select
    Selection.PasteSpecial Paste:=xlValues
    wkbk2.Activate
   
    
    

wkbk2.Close
wkbk.Activate
    

End Sub

Sub Hide_all()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden

Next ws

End Sub

Many thanks.

 

[MARK858]

There is nothing there that looks like it would cause the the behaviour.

You don't have any event code in either the ThisWorkbook module or in any of the sheets (go to the Project -VBAProject window and double click each sheet and ThisWorkbook)?

 

[colzre]

There is nothing there that looks like it would cause the the behaviour.

You don't have any event code in either the ThisWorkbook module or in any of the sheets (go to the Project -VBAProject window and double click each sheet and ThisWorkbook)?

I have the first VBA of the 2 latest I wrote in each sheet to copy and paste values there.

In addition, which I am not sure if would cause any issue, a power query to consolidate all the information of all these sheets (basically each sheet is a month of the year build in a table of numbers then the power query combines all to make for the year round).

Thanks.

 

[colzre]

I have actually found another VBA which does exactly what I needed.

I leave it here if helps anyone:

Sub Unhide_all()

  Dim wks As Worksheet

  For Each wks In Worksheets
      If wks.Visible = xlSheetHidden Then wks.Visible = xlSheetVisible
  Next
End Sub

Thank you.

 

[MARK858]

Happy you have it sorted

 

[RobOrBob]

You could also just save the active worksheet name then activate it after the unhiding:

Sub Unhide_all()

Dim ws As Worksheet
    
Dim aw As String

    aw = ActiveSheet.Name
    
    For Each ws In ActiveWorkbook.Worksheets
        
        ws.Visible = xlSheetVisible
    
    Next ws

    Worksheets(aw).Activate

End Sub

 

[colzre]

You could also just save the active worksheet name then activate it after the unhiding:

Sub Unhide_all()

Dim ws As Worksheet
   
Dim aw As String

    aw = ActiveSheet.Name
   
    For Each ws In ActiveWorkbook.Worksheets
       
        ws.Visible = xlSheetVisible
   
    Next ws

    Worksheets(aw).Activate

End Sub

Great job mate!

I tried an scenario with that possibility and was really helpful

Star!