automatically copy information to different sheet if certain condi

[b.z.]

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 don’t
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.

 

[Gary''s Student]

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.

 

[b.z.]

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]

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

 

[b.z.]

Works perfectly now. Thank you so much!

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

 

[Jack]

I am working in Excel 2007. I have a Workbook with a Worksheet that is a
Master list of vendors and a worksheet that is the output form for the actual
vendors list. The master list consists of 116 rows. In column A you place
an x for the vendors you want to use & leave it blank for the vendors you do
not want to use. Would like a macro that takes the vendors from the master
list that were marked with an x and copy & paste the information in those
rows to the worksheet with the output form while at the same time eliminating
any blank rows. Also, do not want the x to be pasted to the outform for.
Can you help me?

 

[Patrick Molloy]

you can do this with the advanced filter.

dim rw as Long
dim targetRow as long

for rw = 2 to 116
if cells(rw,1)="x" then
targetrow = targetrow+1
rows(rw).copy
worksheets.rows(targetrow).pastespecial xlValues

end if
next rw