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?

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

"Euan Ritchie" wrote:

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?

Why not just make it automatic when you change cell a1. Right click sheet
tabview codecopy\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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Euan Ritchie" wrote in message
...
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?

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

"redeagle" wrote:

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

"Euan Ritchie" wrote:

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?

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

"Don Guillett" wrote:

Why not just make it automatic when you change cell a1. Right click sheet
tabview codecopy\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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Euan Ritchie" wrote in message
...
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?

Cells(r + 5, 2).Select

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

John

"Euan Ritchie" wrote:

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

"redeagle" wrote:

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

"Euan Ritchie" wrote:

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?