Macro should copy & paste to various cells, but won't.

[Guest]

A simple macro to copy a cell to several other cells, will only paste a
specific number of copies. When setting up the macro, I copied one cell to
the next 10 cells below, using the "end" and "arrow down" buttons. Then I
"end arrow down" to the next cell to copy to start the process over, then
stopped recording.

But when the macro runs, it only pastes to exactly 10 cells below the
starting point. I might need 5 cells or 10 or more, but it picks exactly 10
based on my initial instructions.

How does the macro paste down a specific number of cells, using the "end"
and "down arrow" buttons?

 

[Guest]

Post your code...

 

[Don Guillett]

nr=activecell.end(xldown).row+1

Sub copytolast()
lr = Cells(Rows.Count, "e").End(xlUp).Row
Range("e1").Copy Range("e1").Resize(lr, 1)
End Sub

 

[Guest]

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/10/2007
'
' Keyboard Shortcut: Ctrl+a
'
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.End(xlDown).Select
ActiveCell.Offset(3, 0).Range("A1").Select
End Sub

"A1" is the first cell I used to create the macro, not cell "A1"
"A1:A10" copies 10 cells down each time I execute the macro. This needs to
change somehow to a variable quantity, as determined by the "end" and "down
arrow" keys.

Mike

 

[Sandy Mann]

Try removing the line:
ActiveCell.Range("A1:A10").Select

and see what your code does as it is.

I assume that you are filling in between the active cell and the next cell
down with the value in the active cell. If so then try:

Sub PasteIt()
GoDown = ActiveCell.End(xlDown).Row - 1
ActiveCell.Copy Destination:= _
Range(Cells(ActiveCell.Row, _
ActiveCell.Column), Cells(GoDown, _
ActiveCell.Column))

End Sub

Incidentally your code replaces the value in the next cell down from the
active cell.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Having trouble implementing this code in with the other, but I'll keep
trying. Thanks all for the help.

 

[Sandy Mann]

Hi Mike,

I didn't intend that youimplimented both codes together, I was saying that
the line: "ActiveCell.Range("A1:A10").Select" was the line that was causing
your code to paste 10 cells every time.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk