Copy value in one sheet to another sheet

[tvmex_210]

Hello experts,
a newbie hre...

I have a workbook with 2 worksheets namely 'SHEET1' and 'SHEET2'. i have formulas in cells A10,B10 and C10 in SHEET1. The inputs for these formulas vary. Now, i want store the values in these cells to columns A,B and C of SHEET2 respectively, everytime the formula results in a new value. ie, when i input the first set of data , the values in A10,B10 &C10 should be copied to A1,B1 & C1 of SHEET2, then with next set of datas values should be copied to A2,B2 &C2 of SHEET2 and so on....

Is there any way to do this?? It would be great if it could be done with a CommandButton click....


Thanks in advance....plzz help mee...

 

[laughingdevil]

Would it not be easier in sheet two to have
=sheet!a10
etc
in cells a1-c1?

 

[cstimart]

Try this...

Private Sub CommandButton1_Click()
Dim LR As Long
    With Sheets(2)
        LR = .Range("A" & Rows.Count).End(xlUp).Row + 1
        .Range("A" & LR) = Sheets(1).Range("A10").Value
        .Range("B" & LR) = Sheets(1).Range("B10").Value
        .Range("C" & LR) = Sheets(1).Range("C10").Value
    End With
End Sub

 

[tvmex_210]

Thank u very much buddy.. i got it!

Now a little more

If the value in A10 is a duplicate (of values only in column A ,SHEET2) , all the three values must not be entered and an message box should appear...

Can we do that??

Thanks a million

 

[cstimart]

Private Sub CommandButton1_Click()
Dim LR As Long
    With Sheets(2)
        LR = .Range("A" & Rows.Count).End(xlUp).Row + 1
        if .Range("A" & LR - 1) <> Sheets(1).Range("A10").value then
            .Range("A" & LR) = Sheets(1).Range("A10").Value
            .Range("B" & LR) = Sheets(1).Range("B10").Value
            .Range("C" & LR) = Sheets(1).Range("C10").Value
        Else
            Msgbox "Duplicate Values"
        End if
    End With
End Sub

 

[tvmex_210]

Thank you very much ...........