About Us

b.z.[_2_]

Works perfectly now. Thank you so much!

"Gary''s Student" wrote:

My error, not yours. Instead of:
n = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
use:
n = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row+1

--
Gary''s Student - gsnu200762

"b.z." wrote:

Thank you so much. This is great. I have modified your example to fit my
needs (which is impressive since I have next to no knowledge of macros or ms
visual basic) But I seem to be having one problem, in your description you
stated that the "rows data will be copied to the next available row in
sheet2." when the macro copies the data to sheet2 it always copies it to row
1. if there is data in row 1 it will overwrite it. I changed the example as
follows: did something I changed make it do this? and how do I make it so
that it will go to the next empty row?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("O:O")) Is Nothing Then Exit Sub
If Target.Value = "New" Then
Set r1 = Sheets("Sheet1").Range(Target.Offset(0, -14), Target)
n = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
Set r2 = Worksheets("Sheet2").Cells(n, "A")
r1.Copy r2
End If
End Sub

"Gary''s Student" wrote:

This is just an example. Data entry is in Sheet1 and data capture is in
Sheet2.

Data entry is columns A thru E. If "new" is entered in column E, then that
rorw's data will be copied to the next available row in Sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
If Target.Value = "new" Then
Set r1 = Sheets("Sheet1").Range(Target.Offset(0, -4), Target)
n = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
Set r2 = Worksheets("Sheet2").Cells(n, "A")
r1.Copy r2
End If
End Sub

This is an event macro and goes in the worksheet code area, not a standard
module.
--
Gary''s Student - gsnu200762

"b.z." wrote:

Hello,

I am working on compiling a database in excel that I use to input the
information into form letters and such. I put in all the information: company
name, contact name, contact title, address, city, state, zipcode, etc. the
last column I have is a column I will either type the word "new" into or
leave blank. What I want to basically do is if I type the word "new" in the
last column I would like excel to automatically copy the contents of that row
and paste it into a specific worksheet. If I leave the cell blank I dont
want excel to do anything. Is there a macro that I can use for this or
ideally if there was a check box I could check and then have it automatically
copy and paste that would be great.

Thanks in advance.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Data Base, copy information one sheet to another automatically	Bill Bula	Excel Discussion (Misc queries)	2	November 5th 07 01:04 PM
Automatically copy selected information from one worksheet to anot	SM1	Excel Discussion (Misc queries)	1	December 21st 06 01:02 AM
How do I automatically copy contact information?	Miguel	Excel Discussion (Misc queries)	3	September 5th 06 04:50 PM
Extracting information from records to another sheet automatically	Molly	Excel Worksheet Functions	4	February 5th 06 10:43 AM
how can i automatically copy the information in one cell on a she.	Dan	Excel Worksheet Functions	1	November 26th 04 07:03 PM

Menu

About Us