return to the same cell and previous sheet

[MILTINO]

In a book with several sheets from any of them when I want to see a hidden sheet "Authorship" I call it through a macro
class=y2iqfc>In this Sheet "Authorship" I can be the time I give for later when I no longer need to be in this sheet exit and hide this sheet "Authorship" and return to the previous sheet in the same cell from which I went to the Sheet Authorshiplang=EN-US style='font-size:11.0pt;font-family:Arial;mso-ansi-language:EN-US'>
I send a macro this works with the particularity that it always sends me to the first visible sheetlang=EN-US style='font-size:11.0pt;font-family:Arial;mso-ansi-language:EN-US'>
Sub MUESTRA() ‘tecla ctrl+d
Application.ScreenUpdating = False '
Sheets("Autoria").Visible = xlSheetVisible
With ActiveWindow
.DisplayHeadings = False
.DisplayWorkbookTabs = False
Application.DisplayFormulaBar = False
End With
Sheets("Autoria").Select
End Sub

Sub OCULTA()
Application.ScreenUpdating = False
Sheets("Autoria").Visible = xlSheetVeryHidden
With ActiveWindow
.DisplayHeadings = True
.DisplayWorkbookTabs = True
Application.DisplayFormulaBar = True
End With
On Error GoTo error:
error:
End Sub

 

[Alex Blakenburg]

My initial reaction is that you need to store the previous address somewhere either on a sheet on in a Named Range but this might work for you.

Public gReturnToRng As Range

Sub MUESTRA() ' tecla ctrl+d
    Application.ScreenUpdating = False '
    
    Set gReturnToRng = ActiveCell
    Sheets("Autoria").Visible = xlSheetVisible
    With ActiveWindow
        .DisplayHeadings = False
        .DisplayWorkbookTabs = False
        Application.DisplayFormulaBar = False
    End With
    Sheets("Autoria").Select
End Sub

Sub OCULTA()
    Application.ScreenUpdating = False
    Sheets("Autoria").Visible = xlSheetVeryHidden
    With ActiveWindow
        .DisplayHeadings = True
        .DisplayWorkbookTabs = True
        Application.DisplayFormulaBar = True
    End With
    
    If Not gReturnToRng Is Nothing Then Application.Goto gReturnToRng
    On Error GoTo error:
error:
End Sub
	
	
	
	
	
	



	
	

		

	

 

[MILTINO]

Greetings, it gives me an error when I return
If Not gReturnToRng Is Nothing Then

 

[Alex Blakenburg]

The only way I seem to be able to reproduce that error is to run the first macro (MUESTRA) a second time with the active sheet being "Autoria".

Try in the first macro (MUESTRA) making this replacement.

Set gReturnToRng = ActiveCell
If ActiveCell.Parent.Name <> "Autoria" Then Set gReturnToRng = ActiveCell

 

[MILTINO]

Parece que la única forma en que puedo reproducir ese error es ejecutar la primera macro (MUESTRA) por segunda vez con la hoja activa como "Autoria".

Prueba en la primera macro (MUESTRA) haciendo este reemplazo.
[CÓDIGO=rico]
Establecer gReturnToRng = ActiveCell
Si ActiveCell.Parent.Name <> "Autoria", entonces establezca gReturnToRng = ActiveCell
[/CÓDIGO]

gracias excelente solucion
Te veo pronto

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.
Funnily enough, that's not how it looked when I sent it to you