Macro's Pasting into continous rows

[Euan Ritchie]

I'm trying to make a Macro that once i click the button it will copy from A1
and paste into B2. the next time i click the Button i still want it to copy
from A1 but paste in B3, then B4..B5 so on and so forth!
is this possible?

 

[redeagle]

Hi Euan-

There are better ways, but a quick and easy way would be to use a Static
variable.

Sub CopyCell()

Static r As Integer
Range("A1").Select
Selection.Copy
Cells(r + 2, 2).Select
ActiveSheet.Paste
r = r + 1

End Sub

 

[Don Guillett]

Why not just make it automatic when you change cell a1. Right click sheet
tab>view code>copy\paste this. Now when you change cell a1 the data will
goto the next available row in col B

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("a1").Address Then Exit Sub
dlr = Cells(Rows.Count, "b").End(xlUp).Row + 1
Target.Copy Cells(dlr, "b")
End Sub

 

[Euan Ritchie]

How do i alter this so that the first paste would be in row B5 say.

 

[redeagle]

Hi Don-

Good tip. I'm going to steel that for one of my projects and implement a
Select Case.

Select Case Target.Address
Case Range("A1").Address
dlr = Cells(Rows.Count, "b").End(xlUp).Row + 1
Target.Copy Cells(dlr, "b")
Case Else

End Select

John

 

[redeagle]

Cells(r + 5, 2).Select

But a better way is to use Don's suggestion using the Worksheet_Change()
event.

John