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

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

It works, thanks, however, if there are more than two columns, it won't work
out, I added item3 and item4... but it won't capture more than two columns. I
am not good at code, can you please advice how I shall change codes to add
more columns??

Thanks a lot
Hank

"JLatham" wrote:

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

Hank, here is a version of the same code with the same 'risks' as the
previous version, but that can be adapted to any number of columns of
information. Takes a little more work on your part to set it up, but once
done stays done. You can simply change the (1 to 5) definition of both
arrays to deal with as many columns as you need to and set up the individual
elements as required. The For LC loop will take care of itself automatically.

I will offer another way to do this in another post shortly.

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
'
'Set up an array to hold the destination columns
'This is set up for 5 columns to capture, change
'as needed for your information
Dim destColumns(1 To 5) As String
'now fill the elements with column letters
destColumns(1) = "A"
destColumns(2) = "B"
destColumns(3) = "C"
destColumns(4) = "D"
destColumns(5) = "E"
'these tell where the data comes from on this sheet
'I used some different letters to show that they do not
'have to be the same as destColumns() setup
Dim sourceColumns(1 To 5) As String
sourceColumns(1) = "A" ' from A to A
sourceColumns(2) = "D" ' from D to B
sourceColumns(3) = "E" ' from E to C
sourceColumns(4) = "G" ' from G to D
sourceColumns(5) = "H" ' from H to E
'need a variable to go through the arrays
Dim LC As Integer ' to work through the arrays

'test if a change to a single cell in any of the
'Source Columns took place
If Target.Cells.Count 1 Then
Exit Sub ' only work with 1 cell at a time!
End If
For LC = LBound(sourceColumns) To UBound(sourceColumns)
If Target.Column = Range(sourceColumns(LC) & 1).Column Then
'save new/changed entry entry
Worksheets(newEntrySheetName).Range(destColumns(LC ) & _
Rows.Count).End(xlUp).Offset(1, 0) = Target
'no more work to do, can get out of the loop
Exit For
End If
Next ' end of LC loop
End Sub



"Hank" wrote:

It works, thanks, however, if there are more than two columns, it won't work
out, I added item3 and item4... but it won't capture more than two columns. I
am not good at code, can you please advice how I shall change codes to add
more columns??

Thanks a lot
Hank

"JLatham" wrote:

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

Hank,
Here is another possible solution. The way it works is that it "waits"
until a change takes place in one particular column and at that time it moves
all of the information from that same row in defined columns to the
destination sheet. The assumption is that this 'key' column will ALWAYS be
the last column on the row for data entry and so when data is entered into
it, that all the other information to be copied has already been entered.

Private Sub Worksheet_Change(ByVal Target As Range)
'this version examines just one column for a change
'in it and when that changes, all of the colums are
'copied from the source sheet to the destination sheet
'as a group on a single row, helping keep things from
'possibly getting jumbled up.
'the "KeyColumn" should be the column that you would
'enter the very last information item into after all
'of the others are filled with information.
'this KeyColumn will also be listed in the
'sourceColumns() array
Const KeyColumn = "H"
'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 sourceColumns() definitions
'
'Set up an array to hold the destination columns
'This is set up for 5 columns to capture, change
'as needed for your information
Dim destColumns(1 To 5) As String
'now fill the elements with column letters
destColumns(1) = "A"
destColumns(2) = "B"
destColumns(3) = "C"
destColumns(4) = "D"
destColumns(5) = "E"
'these tell where the data comes from on this sheet
'I used some different letters to show that they do not
'have to be the same as destColumns() setup
Dim sourceColumns(1 To 5) As String
sourceColumns(1) = "A" ' from A to A
sourceColumns(2) = "D" ' from D to B
sourceColumns(3) = "E" ' from E to C
sourceColumns(4) = "G" ' from G to D
sourceColumns(5) = "H" ' from H to E
'need a variable to go through the arrays
Dim LC As Integer ' to work through the arrays
Dim nextRow As Long ' will tell where to move to on 2nd sheet

'test if a change to a single cell in any of the
'Source Columns took place
'and test if change was in the KeyColumn
'at the same time
If Target.Cells.Count 1 Or _
Target.Column < Range(KeyColumn & 1).Column Then
Exit Sub ' only works when only 1 cell changes
End If
'a change took place in the KeyColumn, move
'the information from all source columns into
'next available row on the second sheet
'find the next empty row on the destination sheet
nextRow = Worksheets(newEntrySheetName).Range(destColumns(1) & _
Rows.Count).End(xlUp).Row + 1
'move all information from source sheet to the same
'row on the destination sheet when a change took
'place in a particular cell (in the KeyColumn)
For LC = LBound(sourceColumns) To UBound(sourceColumns)
'save new entries
Worksheets(newEntrySheetName).Range(destColumns(LC ) & nextRow) = _
ActiveSheet.Range(sourceColumns(LC) & Target.Row)
Next ' end of LC loop
End Sub



"Hank" wrote:

It works, thanks, however, if there are more than two columns, it won't work
out, I added item3 and item4... but it won't capture more than two columns. I
am not good at code, can you please advice how I shall change codes to add
more columns??

Thanks a lot
Hank

"JLatham" wrote:

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

This is VERY COOL!! Thanks JLatham!!!!!!!
Hank

"JLatham" wrote:

Hank,
Here is another possible solution. The way it works is that it "waits"
until a change takes place in one particular column and at that time it moves
all of the information from that same row in defined columns to the
destination sheet. The assumption is that this 'key' column will ALWAYS be
the last column on the row for data entry and so when data is entered into
it, that all the other information to be copied has already been entered.

Private Sub Worksheet_Change(ByVal Target As Range)
'this version examines just one column for a change
'in it and when that changes, all of the colums are
'copied from the source sheet to the destination sheet
'as a group on a single row, helping keep things from
'possibly getting jumbled up.
'the "KeyColumn" should be the column that you would
'enter the very last information item into after all
'of the others are filled with information.
'this KeyColumn will also be listed in the
'sourceColumns() array
Const KeyColumn = "H"
'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 sourceColumns() definitions
'
'Set up an array to hold the destination columns
'This is set up for 5 columns to capture, change
'as needed for your information
Dim destColumns(1 To 5) As String
'now fill the elements with column letters
destColumns(1) = "A"
destColumns(2) = "B"
destColumns(3) = "C"
destColumns(4) = "D"
destColumns(5) = "E"
'these tell where the data comes from on this sheet
'I used some different letters to show that they do not
'have to be the same as destColumns() setup
Dim sourceColumns(1 To 5) As String
sourceColumns(1) = "A" ' from A to A
sourceColumns(2) = "D" ' from D to B
sourceColumns(3) = "E" ' from E to C
sourceColumns(4) = "G" ' from G to D
sourceColumns(5) = "H" ' from H to E
'need a variable to go through the arrays
Dim LC As Integer ' to work through the arrays
Dim nextRow As Long ' will tell where to move to on 2nd sheet

'test if a change to a single cell in any of the
'Source Columns took place
'and test if change was in the KeyColumn
'at the same time
If Target.Cells.Count 1 Or _
Target.Column < Range(KeyColumn & 1).Column Then
Exit Sub ' only works when only 1 cell changes
End If
'a change took place in the KeyColumn, move
'the information from all source columns into
'next available row on the second sheet
'find the next empty row on the destination sheet
nextRow = Worksheets(newEntrySheetName).Range(destColumns(1) & _
Rows.Count).End(xlUp).Row + 1
'move all information from source sheet to the same
'row on the destination sheet when a change took
'place in a particular cell (in the KeyColumn)
For LC = LBound(sourceColumns) To UBound(sourceColumns)
'save new entries
Worksheets(newEntrySheetName).Range(destColumns(LC ) & nextRow) = _
ActiveSheet.Range(sourceColumns(LC) & Target.Row)
Next ' end of LC loop
End Sub



"Hank" wrote:

It works, thanks, however, if there are more than two columns, it won't work
out, I added item3 and item4... but it won't capture more than two columns. I
am not good at code, can you please advice how I shall change codes to add
more columns??

Thanks a lot
Hank

"JLatham" wrote:

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