macro to move column

[congokin]

i have data that changes dynamically in cola(a1-a10). i want to record each change of data in subsequent columns
step1

Excel Workbook
	A
1	1
2	2
3	3
Sheet4

Excel 2007
step2

Excel Workbook
	A	B
1	4	1
2	7	2
3	9	3
Sheet4

Excel 2007
step3

Excel Workbook
	A	B	C
1	45	4	1
2	6	7	2
3	38	9	3
Sheet4

Excel 2007

the following macro does the job but copy cola 10 times instead of 1 per above example

 

[congokin]

forgot to put the code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LC As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        ' Application.EnableEvents = False
        Range("A1:A10").Copy Cells(1, LC + 1)
        ' Application.EnableEvents = True
    End If
End Sub

any help will be appreciated

 

[VoG]

If the data is refreshed sequentially from A1 to A10 then perhaps

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LC As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
    If Not Intersect(Target, Range("A10")) Is Nothing Then
        ' Application.EnableEvents = False
        Range("A1:A10").Copy Cells(1, LC + 1)
        ' Application.EnableEvents = True
    End If
End Sub

 

[congokin]

thanks this works great

Will it be possble to extend this code so that when cell.value of last colum > cell.value of last colum -1 then color cell.value of last colum - 1 = red else no color