copy what is in the cell where the cursor is without moving the cursor

[still learning]

Hi

When I open a wookbook, and then open a spreadsheet, A Private sub worksheet_open macro sets up a title row and drops down to the last A:A cell that has data ( a date) in it. Then it drops one for me to enter a new date.
I then want an event macro to go up one cell (in “A”) and copy that date to Q2
Then when I enter data in “C” another event macro moves the cursor to “I”
Then when I enter data in “I” another event macro moves the cursor to “J”

“A” are dates
Q2 is part of a xlookup formula

This is what I have so far

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
ActiveCell.Offset(-1, 0).Range("A1").Select
' copy what is in the cell to Q2, then
ActiveCell.Offset(1, 0).Range("A1").Select
' this puts the cursor back to the cell that the new date was entered
If Target.Column = 3 Then
ActiveCell.Offset(0, 6).Range("A1").Select
If Target.Column = 6 Then
ActiveCell.Offset(0, 1).Range("A1").Select
End If
End If
End If
End Sub

This event macro works fine from …If target.column=3..
I’m trying to add the ..column=1 part

I don’t know how to copy the previous date to Q2 with out moving to Q2



mike

 

[My Aswer Is This]

Moving the cursor to perform a Task is not a way to do things.
And what do you plan to change in column 1
Your telling XL that when you make a change to whats in column 1 do something else.
So if you enter anything in column 1 something will happen
Moving the cursor around does nothing.
You should do something like enter "Alpha" in Range("A1") And then have code say Target.offset(,1).value="Me

Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Value = "George" Then Target.Offset(, 3).Value = "A Good Guy"
End If

End Sub

 

[AlphaFrog]

Private Sub Worksheet_Change(ByVal Target As Range)
    'Check if only one cell changed and it's not empty.
    If Target.Count = 1 And Target(1).Value <> Empty Then
        Select Case Target.Column
            Case 1
                'Copy new date to Q2
                Range("Q2").Value = Target.Value
            Case 3
                'After changing column C, select column I
                Target.Offset(0, 6).Select
            Case 9
                'After changing column I, select column J
                Target.Offset(0, 1).Select
            Case Else
                ' Do nothing
        End Select
    End If
End Sub

 

[still learning]

Hi My Answer Is This
After the open macro goes to the first empty cell in "A", I will enter a date. That's when I i want the previous date copied to Q2. then I want the cursor to go back to where i put in the new date. then i move to "B", put in data, them move to "C" and after i put in data, the cursor moves to "I" ..etc.
I didn't know how to get "THAT" previous date copied to Q2 easily.
What I was doing was ...with a macro, i moved the cursor up one cell to that previous date, ranged name the cell "here", went to Q2. copied range "here" to it..Then I went back to range "here", deleted the range name, and dropped down one.

Hi Alphafrog.
Thank you, I now know how to "get that previous date copied without moving the cursor.

Range(Q2).Value=target. value

Unless I'm wrong ( as is many, many times) I'll make the event code run a macro when I put in a new date in A ( which changes A). The macro will move up one cell and copy the previous date to Q2 using part of your code, then it will drop back down and I can continue in the row. Nothing else will change in A.
I'll work on "My macro" during the week and come back if I have a problem.

Thanks to the both of you for your help

mike

in case your wondering...I Have a leased truck and I record the millage when I get gas. I did it for the previous lease and using helper cells and formulas and xlookup, I can see how my millage is doing compared to the last lease.

 

[My Aswer Is This]

There is no script that I know of that will move the cursor.
Now I can have a script that can select a range but that does not move the cursor.

You said: "then I want the cursor to go back to where i put in the new date."
The cursor may be down on the Task bar or any place.

 

[footoo]

There is no script that I know of that will move the cursor.
Now I can have a script that can select a range but that does not move the cursor.

You said: "then I want the cursor to go back to where i put in the new date."
The cursor may be down on the Task bar or any place.

Visual Basic Procedure to Get/Set Cursor Position - Microsoft Support

support.microsoft.com

 

[My Aswer Is This]

Visual Basic Procedure to Get/Set Cursor Position - Microsoft Support

support.microsoft.com

If someone here can help and provide the script as this user wants, I will learn something here.

 

[still learning]

Hi
first, Thanks for trying to help My Answer IS This.
I realized I don’t need an event macro to do something if A:A changes
It doesn’t matter if the previous date gets copied every time I open the sheet, even to just look at it
This is the open macro I use when I open the sheet

Private Sub Worksheet_activate()
opentruck
End Sub

Sub opentruck()
Range("A5").Select
ActiveWindow.FreezePanes = True
x = Range("a" & Rows.Count).End(xlUp).Row
Range("A" & x + 1).Select
ActiveWindow.ScrollRow = ActiveCell.Row – 8
‘ this is the added macro
copyandpaste
End Sub

When I open the worksheet, The cursor is now on the first empty cell in A:A, I added a macro to move up one and copy what is there to Q2 and move back down

Sub copyandpaste()
ActiveCell.Offset(-1, 0).Range("A1").Select
Range("Q2").Value = ActiveCell.Value
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Now I can enter a date or just look at the sheet
I still have the event code to move the cursor when I add data to C:C and I:I



Thank you for showing me how to copy from a cell to a cell without having to move to the cursor.



mike

 

[My Aswer Is This]

Looks like this work.

Sub copyandpaste()
Range("Q2").Value = Range("A1").Value
End Sub

 

[My Aswer Is This]

Thinking you need to move the cursor to perform a task is not correct.

To write code all you need to do is something like this.
Range("A1").value=Range("C67").value

Some people want to copy a range and then paste it some place.
This is only needed if you want to also copy the formatting in the cell.