[RESOLVED] Problems with Worksheet_SelectionChange

[anghel.michael]

Hello,

I am trying to accomplish the following:
I have 2 sheets, and i want to exchange some data between them.
Each time sheet2 is clicked on, i need a script to run.

Let's say sheet1 is selected, and i have the cell C4 active.
Eache time i go to sheet2 i need a script to run automaticly.
The script should take the value from the cell that was active in sheet1(so C4),
and copy in in sheet2, when i switch to it, in another cell, let's say D8.

I figured out i need the Worksheet_SelectionChange(ByVal Target As Range) function.
I guess that "Target", holds the value of the cell that was active in the previous sheet?
How can i access that value?
I tried Target.Value but it didn't work.

If someone could help me i would appriciate it verry much!

Thank you!

[anghel.michael]

I have created a new module and added:

Dim rLastCell As Range
 
 
Sub ExampleProcedure()
    Run "LastUsedCell", 1
        If Not rLastCell Is Nothing Then
            MsgBox rLastCell.Value
        Else
            MsgBox "The Sheet is empty"
        End If
End Sub
 
Function LastUsedCell(lWsIndex As Long, Optional strColumn As String) As Range
 
'''''''''''''Written by www.ozgrid.com''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Returns a Range Object of the last used cell on Worksheet or in a Column.
'Sheet index number (lWsIndex) is mandatory and Column letter (strColumn) is optional.
'Example call from Procedure in the same Module.
''''''''''''''''''''' Run "LastUsedCell", 1, "A" '''''''''''''''''''''''''''''''''''''''
'IMPORTANT. WILL FAIL IF SHEET INDEX USED IS NOT A WORKSHEET.
 
 
    If strColumn = vbNullString Then
        With Sheets(1).UsedRange
            Set rLastCell = .Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
        End With
    Else
        With Sheets(lWsIndex)
            Set rLastCell = .Cells(.Rows.Count, strColumn).End(xlUp)
        End With
    End If
End Function

But i get the value of the cell in the last row&column, not the las active one, any thoughts?

Thank you!

[anghel.michael]

Ok so i declared a global variable for the first sheet, and access it from the 2nd sheet, and now it almost works

Public LastCell As Range
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set LastCell = Target.Select( go down 3 and right 3) so a 3x3 block
End Sub

Can someone please help me on the syntax?

[westconn1]

the selection change event does not fire when changing sheet, only cells within a sheet

try

set lastcell = target.resize(3, 3)

note this sets a range object, does not assign values to a range

[anghel.michael]

that fixed it , thank you!