You can put a copy of the code below into the Worksheet_Change() event
handler for each of your 5 sheets (have to do it for each sheet
individually). Change the new sheet name and the column definitions as
required for your reality.

To put the code where it needs to be for any given sheet, right click on the
sheet's name tab and choose [View Code] from the list that appears. Copy the
code below and paste it into the code module presented to you and make
changes as required to the Const definitions in it. They can be different
for each sheet.

There's lots that can go wrong here, and ways to get data out of sync. This
code simply responds to a change in a column and puts a copy of that change
or new entry into the first available empty cell in the new sheet columns
specified. Easy to get out of sync if you aren't careful; especially if you
make a change to an existing Project # and don't make a corresponding change
to its associated Account or the other way around.

Here's the basic code

Private Sub Worksheet_Change(ByVal Target As Range)
'set up some constants to point to the sheet
'that will save the new entries on this sheet
'and where to save them there,
'also need some to identify what columns of
'information we need to save from this sheet

Const newEntrySheetName = "Sheet3" ' change to name of new sheet
'these determine the columns to put the captured data onto
'in the new sheet, will match with
'similarly named _SourceColumn definitions
Const item1_DestColumn = "A"
Const item2_DestColumn = "B"
'these tell where the data comes from on this sheet
'I used very different letters to show that they do not
'have to be the same as the _DestColumn setups
Const item1_SourceColumn = "G" ' change for Project # column
Const item2_SourceColumn = "V" ' change for Account column

'test if a change to a single cell in either of the
'_SourceColumns took place
If Target.Cells.Count 1 Then
Exit Sub ' only work with 1 cell at a time!
End If
If Target.Column = Range(item1_SourceColumn & 1).Column Then
'save new/changed Project # entry
Worksheets(newEntrySheetName).Range(item1_DestColu mn & _
Rows.Count).End(xlUp).Offset(1, 0) = Target

ElseIf Target.Column = Range(item2_SourceColumn & 1).Column Then
'save new/changed Account entry
Worksheets(newEntrySheetName).Range(item2_DestColu mn & _
Rows.Count).End(xlUp).Offset(1, 0) = Target

Else
'do nothing!
End If

End Sub


"Hank" wrote:

Hi, I have five worksheets I am working on to input data, I would like to
have a new sheet to collect some key fields data that I input so that I can
have a key data list.
such as I have fields "project #" "project name" "Account" "Date", when I
input data in those fields, a new sheet will caputure data in "project #"
"Account" automaticlly.

Thanks a lot

Hank