Copy & Paste From sheet a (L1) to sheet B (A2)

[jeh]

Hi
I want to copy from sheet3 (L1) to Sheet1 (A2). I don't want to over write data that might already be there, If there is data in Sheet1 (A2), then Paste the new data in (A2)an so on. Why didn't anybody tell me about this Board! haha

Thanks for your time,

Jim

[ This Message was edited by: jeh on 2002-04-28 22:58 ]

[Ivan F Moala]

On 2002-04-28 22:57, jeh wrote:
Hi
I want to copy from sheet3 (L1) to Sheet1 (A2). I don't want to over write data that might already be there, If there is data in Sheet1 (A2), then Paste the new data in (A2)an so on. Why didn't anybody tell me about this Board! haha

Thanks for your time,

Jim

[ This Message was edited by: jeh on 2002-04-28 22:58 ]

Hi Jim try;

Sheets("Sheet3").Range("L1").Copy Sheets("Sheet1").Range("A1").End(xlDown).Offset(1, 0)

[Tom Schreiner]

On 2002-04-28 22:57, jeh wrote:
Hi
I want to copy from sheet3 (L1) to Sheet1 (A2). I don't want to over write data that might already be there, If there is data in Sheet1 (A2), then Paste the new data in (A2)an so on.

Hi Jim.
Do you want this automated I guess?
Whenever a value is entered into sheet3 L1,
you want to send the value to the next available row on sheet1 Column A?
Thanks,
Tom

[jeh]

Hi Tom,

Yes, I do. I want to be able to hit a command button that will send this data to the other sheet. I can make the command button, just can't seem to put the horsepower behind it.

Thanks,

Jim

[jeh]

Hi Ivan,
I tried your code and it gives an error. Am I missing a seperator between the copy & Paste statement?

Thanks,

Jim


On 2002-04-28 23:04, Ivan F Moala wrote:

Hi Jim try;

Sheets("Sheet3").Range("L1").Copy Sheets("Sheet1").Range("A1").End(xlDown).Offset(1, 0)

[Tom Schreiner]

Hi Jim...
You do not need a commandbutton for this routine...
From sheet3, right click on your sheet tab, choose view code, and paste this in...
If the value in L1 is changed, the new value will be sent to the first available row on sheet1 in column A...

Tom

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$L$1" Then _

    Sheets("Sheet3").Range("L1").Copy Destination:= _

    Sheets("sheet1").Range("A" & Sheets("sheet1"). _

    Cells(65536, 1).End(xlUp).Row + 1)

End Sub

[jeh]

Hi Tom,

Thank you very much. This is just what I needed (for now).

Jim