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.

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 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.
>

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 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.
> >

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 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.
> > >

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 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.
> > > >

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?
--
Jack Wood


"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 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.
> >

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


"Jack" <(E-Mail Removed)> wrote in message
news4ECFE4B-923A-4DA0-B908-(E-Mail Removed)...
> 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?
> --
> Jack Wood
>
>
> "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 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.
>> >