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]

It's also a good ideal to use "With"

Like this:

Private Sub CommandButton1_Click()
'Using with
Dim ans As String
ans = ActiveSheet.Name

With Sheets(ans)
.Range("A1").Copy Sheets("Alpha").Range("A1")
.Range("G1").Copy Sheets("Alpha").Range("A2")
End With
End Sub

 

[AlphaFrog]

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()
Range("Q2").Value = ActiveCell.Offset(-1, 0).Value
End Sub

Pro tip: you don't have to .Select cells to copy or paste. The code above copies the value from the cell above the active cell to Q2 without selecting any cells.

 

[AlphaFrog]

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.

@ My Answer Is This
I could be wrong, but I think what Still Learning meant by "moving the cursor" is just selecting a different cell. I don't think he/she meant actually moving the mouse cursor. I could be wrong though.