PDA

View Full Version : [SOLVED] Macro to copy cell without leaving cell



selfteaching
09-30-2018, 06:38 AM
Hi
I'm trying to have a macro copy a cell to another cell in the same worksheet without leaving that cell

Example:

Private Sub Worksheet_activate()
Range("A5").Select
ActiveWindow.FreezePanes = True
x = Range("A" & Rows.Count).End(xlUp).Row
' copy this cell to P7
Range("A" & x + 1).Select
End Sub

p45cal
09-30-2018, 11:43 AM
Range("P7").value = x
or
Range("P7").value = Range("A" & Rows.Count).End(xlUp).value

depending on what value you're wanting.

selfteaching
09-30-2018, 12:10 PM
Hi p45cal,
thanks for the quick response
I can't get the first on to work,

I made a test spreedsheet with numbers 1 to 15 in A1:A15
I changed P7to C1
I put the cursor on A12 and ran the macro

Sub test()
Range("P7").value = x
end sub
C1 was empty
What did I do wrong?


BUT>> the second one is perfect for what i need.

p45cal
09-30-2018, 01:06 PM
C1 was empty
What did I do wrong?You didn't assign anything to x as you had in your first message.

selfteaching
09-30-2018, 03:31 PM
It took awhile to figure out that I need something like

Selection.Value
I"ll remember it better because i had to figure it out :yes
Thank you for your help and giving me a learning tool.

I also like your name :clap: