macro to copy paste value from sheet1 to sheet2 in empty cell

[koi]

hi all,


i have a problem with this thing, here is the situation


i have sheet1 name as "data" and has range A1:A5 and i want to assigned a button there, when we press the button, a macro will run copy paste to sheet2 name as "log" A1:A5,


and if someone press the button again, it will copy to sheet2 range b1:b5 (since we have value already in a1:a5)


is that possible? (i know it can since almost nothing is impossible in excel )


thanks for the solution / advice

 

[Luke M]

[pre]

Sub TransferData()
Dim lastCol As Long

With Worksheets("log")
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

'Check if we need to paste in first col, or next col
If .Cells(1, lastCol) <> "" Then
lastCol = lastCol + 1
End If

'Copy the data
Worksheets("Data").Range("A1:A5").Copy .Cells(1, lastCol)

End With

End Sub

[/pre]

 

[lhkittle]

Just to add to Luke M's code in case the column A length varies in sheet Data...

[pre]

Sub TransferData()
Dim lastCol As Long

With Worksheets("Log")
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

'Check if we need to paste in first col, or next col
If .Cells(1, lastCol) <> "" Then
lastCol = lastCol + 1
End If

'Copy the data
Worksheets("Data").Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Copy .Cells(1, lastCol)

End With

End Sub

[/pre]

Regards,

Howard

 

[koi]

hi Luke & howard,


thanks for the solution, it works like the way i wanted


regards,

koi