macro to copy cell without moving

still learning

Well-known Member
Joined
Jan 15, 2010
Messages
587
Office Version
365
Platform
Windows
Hi
I'm stuck on how to have a macro copy and paste something without leaving the cell that the cursor is in

after the sheet opens and the cursor lands on the next empty cell in a, I want to copy what is in H above that row to Z1 but i want to stay in that A cell and add input.
I don't know how to keep the cursor in that A cell.
I have:
HTML:
Private Sub Workbook_open()
x = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & x + 1).Select
ActiveWindow.ScrollRow = ActiveCell.Row - 8

Selection.End(xlToRight).Select
    ActiveCell.Offset(0, -1).Range("A1").Select
    Selection.Copy

' this is where i'm stuck  the next line doesn't keep the cursor from moving
    Range("z1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Selection.Value = Selection.Value
.....
mike




.....
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,583
Office Version
2013, 2007
Platform
Windows
Copy A2 to C4:

Code:
Range("A2").Copy Range("C4")
or
Code:
Range("C4").Value = Range("A2").Value
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,047
Office Version
2016
Platform
Windows
Code:
Private Sub Workbook_open()
x = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & x + 1).Select
ActiveWindow.ScrollRow = ActiveCell.Row - 8


Range("z1") = Cells(x, "H").Value
 

still learning

Well-known Member
Joined
Jan 15, 2010
Messages
587
Office Version
365
Platform
Windows
Thanks footoo, jolivanes
both are exactly what I needed


mike (y)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,933
Messages
5,508,195
Members
408,670
Latest member
lhmwnrexcel

This Week's Hot Topics

Top