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

[xmp1]

Module 1
  Public LastWS as String

Sheet 3
(Activate Event)
   LastWS = Me.Codename

Sheet 2
(Activate Event)
  LastWS = Me.Codename

Sheet 1
(Activate Event)
 Sheet1.Range("A3").Value = LastWS.Range("A2").Value

And yes the value of LastWS exists before the activate event occurs for Sheet1.

I have even tried cstr() with Sheets() and then Worksheets(LastWS) but nothing I try works. Everything gets thrown a subscript error. I need to use a variable to reference the worksheet by string because the last active sheet changes and that value determines what happens next.

Bear in mind the reference to the object will be inside a for/next loop as well. Thank you.

[mdmackillop]

'Module 1
Public LastWS As Worksheet

'Sheet Modules 2 & 3
Private Sub Worksheet_Activate()
Set LastWS = ActiveSheet
End Sub

'Sheet Module 1
Private Sub Worksheet_Activate()
Range("A3").Value = LastWS.Range("A2").Value
End Sub

[Paul_Hossler]

You're using LastWS as if it were an object of Type Worksheet

Sheet1.Range("A3").Value = LastWS.Range("A2").Value

LastWS is a string with a .CodeName in it

I think you wanted to use a string as an index / subscript into the Worksheets collection like this

Sheet1.Range("A3").Value =Worksheets( LastWS).Range("A2").Value

Personally, I think Mac's approach is cleaner since it strictly deals with the objects directly

[xmp1]

Guys here's the problem. I have tried both of these methods. It keeps throwing a Object Variable or With Block Not Set. Here is a look at the current IDE.

Private Sub Worksheet_Activate()
If LastWS.CodeName = "" Then Exit Sub
MsgBox LastWS.CodeName
For x = 1 to 3
Sheet1.Range("A" & x).Value = LastWS.Range("A" & x).Value
Sheet1.Range("B" & x).Value = LastWS.Range("B" & x).Value
Sheet1.Range("C" & x).Value = LastWS.Range("C" & x).Value
Next x
End Sub

Public LastWS As Worksheet (Module 1)

Even with MsgBox LastWS.Codename alone in the sub it throws the same error.

[xmp1]

So evidently it looks like even though you use Set LastWS = ActiveSheet in the other deactivate events for other sheets.. it seems the value is not being stored? Because if I use the following code it works.

Private Sub Worksheet_Activate()
Set LastWS = Sheet3
MsgBox LastWS.CodeName
Sheet1.Range("A3").Value = LastWS.Range("A3").Value
End Sub

[xmp1]

So on the activate event

Set LastWS = ActiveSheet

works fine but then once you deactivate the worksheet the value seems to be lost switching back to Sheet1 from any sheet in between 2 and 5 (5 total)

Because I get an object with error unless I use Set LastWS in Sheet1 which is where the code above will compile

[xmp1]

So the solution appears to be

Activate a sheet when the workbook is open and all seems to work accordingly

[SamT]

Module 1 Code

Dim LastWs As Worksheet

Sheets 2 and 3 Code

Private Sub Worksheet_Activate()
   Set LastWs = Me
End Sub

Sheet1 code

Private Sub Worksheet_Activate()
MsgBox LastWS.CodeName


For x = 1 to 3
Sheet1.Range("A" & x).Value = LastWS.Range("A" & x).Value
Sheet1.Range("B" & x).Value = LastWS.Range("B" & x).Value
Sheet1.Range("C" & x).Value = LastWS.Range("C" & x).Value
Next x

Set LastWs = Nothing
End Sub

BTW, I'm not sure that you can refer to an object in the Sheets Collection by CodeName

I gave Sheet1 a codename of Sheet11, but

X = Sheets("Sheet11").Name

Raises an Error.

[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