macro for copying values to the next blank cell

[excelNewbie22]

hi,
i tried recording a macro for copying a range of cells,
then goes to the bottom of the table,
to the next empty line
and paste only values,
and it worked well,
but when i trying running it again,
it overwrites the last paste, why?

code:

Sub copy2()
'
' copy2 macro
'

'
    Range("L2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.copy
    Selection.End(xlToLeft).Select
    Range("L2").Select
    Selection.End(xlDown).Select
    Range("L7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("L8").Select
End Sub

and also,
can anyone help me out with a macro that change a range in a cell?
like if a9 is:
=a20:g50
i want the macro to change it to:
a19:g50
and so on, subtract 1 every macro run (a18 then a17....)
?

 

[Micron]

when i trying running it again,
it overwrites the last paste, why?

Because your range references don't change. Consider InputBox (type8) to capture either the range to copy, or the range to paste onto (even if that is just the top left cell of the target range) or both.

subtract 1 every macro run (a18 then a17....)

Depends - not hard but if you close the workbook then what? You'll begin with the same seed value all over again (e.g. 20). To avoid that you would have to store the seed value in a helper cell, then edit it as you go.

 

[kvsrinivasamurthy]

Try.

Sub copy2()
'
' copy2 macro
'

'
    Range("L2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.copy
    Range("L2").End(xlDown).offset(1,0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End Sub

 

[Peter_SSs]

Generally using 'Select' in code in not required and slows your code so I suggest that you try to avoid that in the future.
If I have understood your requirement correctly it might just need this

Sub copy2_v2()
  With Range("L2", Range("L2").End(xlToRight))
    Cells(Rows.Count, .Column).End(xlUp).Offset(1).Resize(, .Columns.Count).Value = .Value
  End With
End Sub

and also,
can anyone help me out with a macro that change a range in a cell?
like if a9 is:
=a20:g50

This needs clarification please
A20:G50 is a range of 31 rows.
If =A20:G50 is a formula placed in cell A9 it will result in a #SPILL! error because the formula is only 11 rows above the first row of the A30:G50 range being referenced.
Also if the 20 is reduced 11 times then the formula would be referring to its own cell causing a circular reference error.

.. or have I misunderstood what you have, where it is and what you want?

 

[excelNewbie22]

Micron
i don't know how to read too much vba, only to record one, so forgive my ignorance in the subject but i didn't understand much (like InputBox (type8) )
kvsrinivasamurthy
tested and works great, thanks!
Peter_SSs
also works great! thanks!
as for clarification:
you right, i just wrote an example without much thought,
but the original would not spill, and the formula wouldn't refers to its own cell

since kvsrinivasamurthy answer correctly i would mark his answer and open a new thread to ask

thank u all!

 

[Micron]

i didn't understand much (like InputBox (type8)

Fair enough. Maybe next time take a suggestion like that and look it up? It might not be the best approach at the time but is likely related and you're bound to learn something that might be useful later. Glad you got a solution
Top Google result for ms excel input box