Module 1 Code
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.