adding data to sheet 2 from sheet 1

I have two sheets, one that I add information to and one that is a summary of the completed information from sheet 1. Once I add 'completed' into column A, I want that row's data to paste into the next available row in sheet 2. So, what I would have in sheet one is everything but in sheet 2 is just the completed items. Is there a way to do this?

Thank you,
Jaime

I have no idea what level of programmer you are so I will write this as
though you have a good basic knowledge of Excel Macros/VBA.

I believe the easiest way to do this would be to record a macro doing what
you want to happen when you enter 'completed' into column A. The macro can
also be recorded with the relative reference setting to help in the
automation effort.

Then, from within VBA, select the sheet in the Project Explorer that you
enter your information on. This should bring up a code window. You will
need to insert a new function as follows:
Private Sub Worksheet_Change(ByVal Target As Range)

Then, you need to write code that will check 'Target' and see if the current
cell is in column A, and is equal to 'Completed'. For the case of 'True'
copy and paste the macro code you recorded earlier. A couple of items will
need to be programmed though. You will need to make sure the cell
references are now based around the line that 'Target' is on. You will also
need to check your destination sheet and identify where you are going to
insert this line.

There are a couple of other checks that are good for dummy proofing your
code. Check and make sure that the selection is only one cell. You don't
what the macro to even run when multiple cells are selected.

I have done exactly this type of thing several times. If you need more
help. Just ask.

Eric
"jegal76" <jegal76.1wuiyb_1129215905.136@excelforum-nospam.com> wrote in
message news:jegal76.1wuiyb_1129215905.136@excelforum-nospam.com...
>
> I have two sheets, one that I add information to and one that is a
> summary of the completed information from sheet 1. Once I add
> 'completed' into column A, I want that row's data to paste into the
> next available row in sheet 2. So, what I would have in sheet one is
> everything but in sheet 2 is just the completed items. Is there a way
> to do this?
>
> Thank you,
> Jaime
>
>
> --
> jegal76
> ------------------------------------------------------------------------
> jegal76's Profile:
> http://www.excelforum.com/member.php...o&userid=28077
> View this thread: http://www.excelforum.com/showthread...hreadid=475834
>

The following macro will copy Columns B:C of the same row whenever the word
Completed is entered into Column A. All this in the active sheet. It will
then paste this into the next empty row in Sheet "Two" in Columns A:B. I
suggest that you use Data Validation in the Column A cells with which to
enter the word Completed to preclude an erroneous entry (spelling, spaces,
etc).
Note that this macro is a sheet event macro and must be placed in the
sheet module of the active sheet. To do this, right-click on the tab of the
active sheet, select View Code, and paste this macro into that module.
Please post back if you need more. HTH Otto

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target.Column <> 1 Then Exit Sub
If Target.Value = "Completed" Then _
Target.Offset(, 1).Resize(, 2).Copy _
Sheets("Two").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

"jegal76" <jegal76.1wuiyb_1129215905.136@excelforum-nospam.com> wrote in
message news:jegal76.1wuiyb_1129215905.136@excelforum-nospam.com...
>
> I have two sheets, one that I add information to and one that is a
> summary of the completed information from sheet 1. Once I add
> 'completed' into column A, I want that row's data to paste into the
> next available row in sheet 2. So, what I would have in sheet one is
> everything but in sheet 2 is just the completed items. Is there a way
> to do this?
>
> Thank you,
> Jaime
>
>
> --
> jegal76
> ------------------------------------------------------------------------
> jegal76's Profile:
> http://www.excelforum.com/member.php...o&userid=28077
> View this thread: http://www.excelforum.com/showthread...hreadid=475834
>

Thank you very much for your help. I was able to paste the code you gave me into the VBA window but am not sure how to close/save it so it will do what I want. What else do I have to do here? Do the sheets have to be named something specific?

Yes, as written, the second sheet has to be named "Two" without the quotes.
Of course, you can name the sheet anything you want to and just change the
sheet name in the macro to match.
When you paste the macro into the proper module, that macro becomes a
part of the file. Simply save the file.
As I stated, this macro is an event macro. That means it will execute
automatically when a specific event occurs. In this case, that event is any
change in the content of any cell in the sheet. When that event occurs, the
macro will fire. When it fires, it will check if the entry is blank or if
the entry is in Column A. If the entry is blank or NOT in Column A, nothing
will happen. If the entry is not blank and is in Column A, the macro will
check if the entry is "Completed". If it is, then the macro will copy and
paste as you stated you want. If it isn't, nothing will happen.
This can be confusing if you are not familiar with macros. If you wish,
send me an email giving your email address and I will send you a small file
with the macro properly placed. Better yet, send me a small file clearly
showing what you have and clearly showing what you want to happen. Include
the macro I gave you. My email address is ottokmnop@comcast.net. Remove
the "nop" from this address. HTH Otto
"jegal76" <jegal76.1xmdqc_1130515506.0972@excelforum-nospam.com> wrote in
message news:jegal76.1xmdqc_1130515506.0972@excelforum-nospam.com...
>
> Thank you very much for your help. I was able to paste the code you
> gave me into the VBA window but am not sure how to close/save it so it
> will do what I want. What else do I have to do here? Do the sheets
> have to be named something specific?
>
>
> --
> jegal76
> ------------------------------------------------------------------------
> jegal76's Profile:
> http://www.excelforum.com/member.php...o&userid=28077
> View this thread: http://www.excelforum.com/showthread...hreadid=475834
>