Begin macro in same cell as entered data upon exit

[MSKralik]

I have a macro that needs to begin in the same active cell into which data was just entered. Upon exit from the cell using tab, arrow keys, or enter, the macro begins from the adjacent cell just moved into. How do I make the macro start from the original cell I was just in? Note: The macros function is to copy the original cell data from Sheet 1 and paste it into the identical coordinate cell location on Sheet 2, then return to the Selection.Address in Sheet 1.

Thank you for your help.

 

[NoSparks]

You need to do a web search for Worksheet_Change Event

 

[MSKralik]

Hi NoSparks...
Thank you for the direction, but that is where I have been looking. I just have not found a way to begin may the Worksheet_Change Event by storing the current address of the cell so that I can first move back into that cell before any other actions occur.

 

[MSKralik]

So the code I am using is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("F7:F5000")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
RunCalculation
End Sub

And then....

Sub RunCalculation()
    Dim vSHTs As Variant, j As Long, k As Long, l As Long, a As String
    ReDim aSHTs(1 To (Sheets.Count - 1))
   
    l = ActiveSheet.Index
    vSHTs = Array(2)
    a = Selection.Address
    Selection.Copy
    Sheets(vSHTs).Select
    Sheets(vSHTs(0)).Activate
    Sheets(vSHTs(0)).Range(a).Select
    ActiveSheet.Paste
    'Application.CutCopyMode = False
    Sheets(l).Activate
   
    ActiveCell.Select
    Range(a).Offset(0, -4).Select
    'Dim vSHTs As Variant, j As Long, k As Long, l As Long, a As String
    ReDim aSHTs(1 To (Sheets.Count - 1))
    l = ActiveSheet.Index
    vSHTs = Array(2)
    a = Selection.Address
   
    Selection.Copy
    Sheets(vSHTs).Select
    Sheets(vSHTs(0)).Activate
    Sheets(vSHTs(0)).Range(a).Select
   
    ActiveSheet.Paste
    Sheets(l).Activate
End Sub

The RunCalculation Sub works fine, but I need it to start on the cell in which the data was entered.
Thanks...

 

[NoSparks]

OK, the way I see it your issue isn't really the cell that changed, which is the Target that triggered the Worksheet_Change event, it's how to send that address to the other macro.
try this

Private Sub Worksheet_Change(ByVal Target As Range)
'limit change to a single cell
If Target.CountLarge > 1 Then Exit Sub
'limit to being within desired range
If Not Intersect(Target, Range("F7:F5000")) Is Nothing Then
    ' feed target address to the other macro
    RunCalculation (Target.Address(0, 0))
End If
End Sub

and alter the other sub to this

Sub RunCalculation(a As String)
    Dim vSHTs As Variant, j As Long, k As Long, l As Long   ', a As String
    ReDim aSHTs(1 To (Sheets.Count - 1))
   
    l = ActiveSheet.Index
    vSHTs = Array(2)
    'a = Selection.Address
    'Selection.Copy
    Range(a).Copy
    Sheets(vSHTs).Select
    Sheets(vSHTs(0)).Activate
    Sheets(vSHTs(0)).Range(a).Select
    ActiveSheet.Paste
    'Application.CutCopyMode = False
    Sheets(l).Activate
   
    'ActiveCell.Select
    Range(a).Offset(0, -4).Select
    'Dim vSHTs As Variant, j As Long, k As Long, l As Long, a As String
    ReDim aSHTs(1 To (Sheets.Count - 1))
    l = ActiveSheet.Index
    vSHTs = Array(2)
    a = Selection.Address
   
    Selection.Copy
    Sheets(vSHTs).Select
    Sheets(vSHTs(0)).Activate
    Sheets(vSHTs(0)).Range(a).Select
   
    ActiveSheet.Paste
    Sheets(l).Activate
    
    Application.CutCopyMode = False
    
End Sub

 

[MSKralik]

Hi Again...

First... Thank you for taking time with the code. It still does not work however. To get the primary Sub to work, I still had to have the following statements:

Sub RunCalculation()
Dim vSHTs As Variant, j As Long, k As Long, l As Long
ReDim aSHTs(1 To (Sheets.Count - 1))

l = ActiveSheet.Index
vSHTs = Array(2)
a = Selection.Address
Selection.Copy
Sheets(vSHTs).Select
Sheets(vSHTs(0)).Activate
Sheets(vSHTs(0)).Range(a).Select
ActiveSheet.Paste

Sheets(l).Activate

ActiveCell.Select
Range(a).Offset(0, -4).Select
'Dim vSHTs As Variant, j As Long, k As Long, l As Long, a As String
ReDim aSHTs(1 To (Sheets.Count - 1))
l = ActiveSheet.Index
vSHTs = Array(2)
a = Selection.Address

Selection.Copy
Sheets(vSHTs).Select
Sheets(vSHTs(0)).Activate
Sheets(vSHTs(0)).Range(a).Select

ActiveSheet.Paste
Sheets(l).Activate

Application.CutCopyMode = False
End Sub

The worksheet Sub give the following compile error: "Wrong number of arguments or invalid property assignment" and highlights the RunCalculator (Target.Address(0, 0))

Private Sub Worksheet_Change(ByVal Target As Range)
'limit change to a single cell
If Target.CountLarge > 1 Then Exit Sub
'limit to being within desired range
If Not Intersect(Target, Range("F7:F5000")) Is Nothing Then
' feed target address to the other macro

RunCalculation (Target.Address(0, 0))

ActiveCell.Select
RunCalculation

End If
End Sub

Again... Thank you for any help you can provide.

 

[NoSparks]

it should be
Sub RunCalculation(a As String)

 

[MSKralik]

Thank you so very much... It works perfectly! Sorry I missed the "a As String" portion.
Thanks again

 

[NoSparks]

Actually, based on your original post where you said

Note: The macros function is to copy the original cell data from Sheet 1 and paste it into the identical coordinate cell location on Sheet 2, then return to the Selection.Address in Sheet 1.

and then seeing that your sub then offsets for one more copy-paste,
if this were my project, and it isn't, this is all I would use

Private Sub Worksheet_Change(ByVal Target As Range)
    'limit change to a single cell
    If Target.CountLarge > 1 Then Exit Sub
    'limit to being within desired range
    If Not Intersect(Target, Range("F7:F5000")) Is Nothing Then
        ' copy target and offset to same cells on sheet(2)
        With Sheet2.Cells(Target.Row, Target.Column)
            .Value = Target.Value
            .Offset(, -4).Value = Target.Offset(, -4).Value
        End With
    End If
End Sub

FYI:
Be aware that there are 3 different ways to refer to sheets and they don't necessarily all refer to the same sheet
1.) Sheets("Sheet5") refers to the sheet with Sheet5 as the tab name
2.) Sheet5 refers to the sheet with Excel code name Sheet5
3.) Sheets(5) refers to the fifth sheet in the tab line-up including any sheets that may be hidden. (This is the way your sub uses)

But anyway if you're happy, I'm happy and thanks for reporting back.

 

[MSKralik]

This is certainly cleaner. Thanks for the education and guidance.
Have a great day!