How can you use a variable to reference a worksheet object?

[Paul_Hossler]

Maybe try this

In a standard module

Option Explicit

Public LastWS As Worksheet

In ThisWorkbook

Option Explicit

Private Sub Workbook_Open()
    Set LastWS = ActiveSheet
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    MsgBox "Leaving " & LastWS.Name & " for " & Sh.Name
    
    If Sh Is Sheet1 Then
        Sheet1.Range("A3").Value = LastWS.Range("A2").Value
    End If
    
    
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Set LastWS = Sh
End Sub