VBA To Return To Last Sheet

[JADownie]

I have a workbook with 8 sheets. On each department sheet, I have hyperlinks which direct the user to the raw data from which the report was created.

I wanted to have a button on my raw data sheet which would be a "Go Back" button and take the user back to last sheet they were on (when they clicked the hyperlink).

I have the code below that does this to some extent, but I cannot get it to work for my situation here. This code I believe references the last worksheet clicked on, and ignores the hyperlink all together.

Is it possible to tweak my code below to take into consideration hyperlinks?



I have the below macro which is executed by pressing a form button:

Global WhtSheet As String, LastSheet As String
Sub GoBack()
Sheets(LastSheet).Select
End Sub

Additionally, I have the code below on each sheet in my workbook.

Private Sub Worksheet_Activate()
LastSheet = WhtSheet
WhtSheet = ActiveSheet.Name
End Sub


Thanks for any assistance or feedback with this request.

 

[jbeaucaire]

Maybe like this in a regular module:

Global LastSheet As String

Sub GoBack()
   Sheets(LastSheet).Activate
End Sub

Then put this in the ThisWorkbook module:

Sub Workbook_SheetDeactivate(ByVal sh As Object)
    LastSheet = sh.Name
End Sub

This is all you need for the GoBack macro to work, no need to put macros in every sheet module.

 

[JADownie]

I am getting a runtime error here:



Sub GoBack()
Sheets(LastSheet).Activate
End Sub


But I am not sure why....

 

[lenze]

You shouldn't. That works fine for me!!
lenze

 

[jeran042]

Maybe like this in a regular module:

Global LastSheet As String

Sub GoBack()
   Sheets(LastSheet).Activate
End Sub

Then put this in the ThisWorkbook module:

Sub Workbook_SheetDeactivate(ByVal sh As Object)
    LastSheet = sh.Name
End Sub

This is all you need for the GoBack macro to work, no need to put macros in every sheet module.

This worked great for me, thank you!

 

[skillet]

This looks to be what I have been looking for, for many hours over days. I was able to get it working with details in post #6 this is so great! https://www.pcreview.co.uk/threads/excel-go-to-previous-sheet.3844744