Event Handling Code

[Geronimo]

I'm writing an Excel macro to copy a range from one worksheet in a workbook to another sheet in the same workbook. I'm having trouble recalling how to write some event handling code. I ideally wish to trigger the range copying when I enter a value in the last cell in the range on the first worksheet. I would settle for another method if my ideal method is not possible. Thank you for your kind help & prompt reply.

 

[Colo]

Hi Geronimo.

<TABLE BORDER=1>
<TR><TD BGCOLOR="C0C0C0"> </TD><TD BGCOLOR="C0C0C0"> A </TD><TD BGCOLOR="C0C0C0"> B</TD><TD BGCOLOR="C0C0C0"> C</TD></TR>
<TR><TD BGCOLOR="C0C0C0">1</TD><TD>data_A1</TD><TD>data_B1</TD><TD>data_C1</TD></TR>
<TR><TD BGCOLOR="C0C0C0">2</TD><TD>data_A2</TD><TD>data_B2</TD><TD>data_C2</TD></TR>
<TR><TD BGCOLOR="C0C0C0">3</TD><TD>data_A3</TD><TD>data_B3</TD><TD>data_C3</TD></TR>
<TR><TD BGCOLOR="C0C0C0">4</TD><TD>data_A4</TD><TD>data_B4</TD><TD>data_C4</TD></TR>
<TR><TD BGCOLOR="C0C0C0">5</TD><TD>data_A5</TD><TD>data_B5</TD><TD BGCOLOR="CCFFCC">data_C5</TD></TR>
</TABLE>
If last cell is C5 like above.

<pre>
<FONT COLOR="BLUE">'//Please copy this into sheet module of "first worksheet"</FONT>
Private Sub Worksheet_Change(ByVal Target As Range)
With Range("C5")
If Application.Intersect(.Item(1), Target) Is Nothing Then Exit Sub
.CurrentRegion.Copy Sheets("Sheet2").Range("A1") <FONT COLOR="BLUE">'Change Sheet2 to name of another sheet</FONT>
End With
End Sub
</pre>

 

[Legacy 98055]

Hey Colo?
How did you get the image of the worksheet into your message?
Great tool to help other!
First I have seen it...
Thanks
Tom

 

[Geronimo]

Thanks, I'll give it a try to see how it works. I appreciate the quick reply!
This message was edited by Geronimo on 2002-03-30 07:47

 

[Colo]

Hi. TsTom,
This time I only wrote HTML in my reply.
>Great tool to help other!
But I have an excel tool that is Japanese version.

http://qchan.hornet.co.jp/Qchan/Tomoko/FreeSoft/default.asp
0019 table converter(ver2.1)
0019�uƒe�[ƒuƒ‹‚±‚ñ‚Î�[‚½�iVer. 2.1�j�v

A person who made it is my friend, so I ask her if possible make a English version.

regards,

 

[Geronimo]

It didn't work Colo. I filled in the range & sheet names to match my spreadsheet, but to no avail. I'll keep trying, unless you have another suggestion. Thanks for you kind help!

 

[Legacy 98055]

Thanks Colo, I appreciate it!
Tom