Macro to copy cell without leaving cell

[selfteaching]

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]

Range("P7").value = x
or
Range("P7").value = Range("A" & Rows.Count).End(xlUp).value

depending on what value you're wanting.

[selfteaching]

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]

C1 was empty
What did I do wrong?

You didn't assign anything to x as you had in your first message.

[selfteaching]

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
Thank you for your help and giving me a learning tool.

I also like your name