Go back to previous active sheet

To Add Excel VBA Code to a Workbook / This Workbook Module

1. Copy the code that you want to use
2. Select the workbook in which you want to store the code
3. Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
4. In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
5. Right-click on the ThisWorkbook object, and choose View Code
6. Where the cursor is flashing, choose Edit | Paste

Dim wPrev As Worksheet
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Set wPrev = Sh
End Sub
Sub GoBack()

On Error Resume Next
wPrev.Select

End Sub

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Originally Posted by gopal baheti

I want to use it as an Addin so that i can use it on all workbooks

Then it requires Class Module level of coding I believe, and I don't have that much of expertization

You don't need a Class module (*cough*) or an Add-In.

If you put this in the ThisWorkbook code module of your PersonalMacroWorkbook, the sub BounceBack will return you to the most recently used worksheet from any open workbook to any open workbook.

After copy pasting, it will not work until you close your Excel and re-open it. Edit the short-cut key to your taste.

' in ThisWorkbook code module (of Personal Macro Workbook)

'Option Private Module

Public WithEvents myApp As Application
Public LastViewedSheet As Worksheet

Private Sub myApp_SheetDeactivate(ByVal Sh As Object)
    Set LastViewedSheet = Sh
End Sub

Private Sub myApp_WorkbookDeactivate(ByVal Wb As Workbook)
    Set LastViewedSheet = Wb.ActiveSheet
End Sub

Private Sub Workbook_Open()
    Set myApp = Me.Application
    Application.MacroOptions Macro:="PersonalMacroWorkbook.xlsm!ThisWorkbook.BounceBack", _
        Description:="", ShortcutKey:="y"
End Sub

Sub BounceBack()
    If Not ThisWorkbook.LastViewedSheet Is Nothing Then
        On Error GoTo myError
        ThisWorkbook.LastViewedSheet.Activate
        Exit Sub
    End If
myError:
    Beep
End Sub

(*cough*) ThisWorkbook, sheet and user form code modules are all code modules of Objects and can accept the kind of coding that a Class Module can take. In a sense, the built in Excel object code modules are Class modules.