Excel Spreadsheet

[july]

I have created a form on worksheet 1....I want to enter the data on worksheet
2 to automatically populate on the form in worksheet 1... but keep a running
tally of the data in worksheet 2 which is the raw material..... example: in
worksheet 2 column a1, would be the customer first name, column a2 would the
customer phone number, column a3 would be the customer address, column a4
would be the customer city, state, zip..... as i enter the raw data from
worksheet 2 to automatically populate into worksheet 1 (which I did - just
fine) but now my problem is keeping the data in the ROWS column example: row
1 - customer name, customer phone, customer address, customer city state zip
(across the top rows) to display in a spreadsheet format.... so that I
can see that I have completed this form for this customer... thank you...
Please let me know if you can assist me... I know that the form (worksheet
1) will change as the data from (worksheet 2) is enter, and that is fine....
i just want the data stay in the worksheet 2 as the spreadsheet.... so in the
future, I can go to row 115 and see the data entered... I know someone knows
how this is completed..

 

[JLatham]

If I understand this correctly, I believe there are at least 2 ways to arrive
at a solution.

First, lets see if I understand correctly: on Sheet2 you intend to enter
customer information in rows across the sheet. When you have a particular
row selected (or a cell within that row), you want to see that row's
information in your form on Sheet1. Is that pretty much it?

Both of my solutions involve some VBA code (a macro) - specifically code
associated with Sheet2 and it's _SelectionChange event processor. Since I
don't see anything in your data on Sheet 2 that readily translates to a truly
unique identifier, like a Customer Number (although a phone number might be
unique, but can't be guaranteed to be), the solution I'd take would be to
have the _SelectionChange() event pick up on the row number you've just
chosen and simply copy the information from specific columns on that row to
designated cells in your 'form' on Sheet1.
Here's code I think you'll be able to modify easily enough. It goes into
the data sheet's code segment - so put it there, select the data sheet
(Sheet2) and 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 module
that's presented to you. Edit the 2 sheet names as required, and add more
data movement statements as needed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'change these sheet names as required
Const formSheetName = "Sheet1"
Const dataSheetName = "Sheet2"
Dim formSheet As Worksheet
Dim dataSheet As Worksheet
If Target.Rows.Count > 1 Then
'quit if more than 1 row is involved in the selection
Exit Sub
End If
'move the information from Sheet2 over to Sheet1
Set formSheet = ThisWorkbook.Worksheets(formSheetName)
Set dataSheet = ActiveSheet ' this sheet
'move the information from Sheet2 over to Sheet1
'add more code and change source (data sheet) column references
'and destination (form sheet) cell addresses as required
'
'move data from current row, Column A
'to form sheet cell B2
formSheet.Range("B2") = dataSheet.Range("A" & Target.Row)
'move data from current row, column B
'to form sheet cell A3
formSheet.Range("A3") = dataSheet.Range("B" & Target.Row)
'move data from current row, column C
'to form sheet cell D4
formSheet.Range("D4") = dataSheet.Range("C" & Target.Row)
'continue this for as many cells as you need to deal with

'when you're finished with the data moving...
'cleanup and release resources back to the system
Set formSheet = Nothing
Set dataSheet = Nothing
End Sub

 

[july]

Hi JLatham:
I have tried this code made some modifications... it still did not work for
me... however, I want to thank you very much, because of this, I am looking,
exploring, learning VISUAL BASICS..... I had no knowledge on this matter. I
can't get the second (and the rest) of the column to stay on the spreadsheet
while the form change the information. I will keep working on it, unless you
know what I am doing wrong?
thank you again.
July
*************************************************************

If I understand this correctly, I believe there are at least 2 ways to arrive
at a solution.

First, lets see if I understand correctly: on Sheet2 you intend to enter
customer information in rows across the sheet. When you have a particular
row selected (or a cell within that row), you want to see that row's
information in your form on Sheet1. Is that pretty much it?

Both of my solutions involve some VBA code (a macro) - specifically code
associated with Sheet2 and it's _SelectionChange event processor. Since I
don't see anything in your data on Sheet 2 that readily translates to a truly
unique identifier, like a Customer Number (although a phone number might be
unique, but can't be guaranteed to be), the solution I'd take would be to
have the _SelectionChange() event pick up on the row number you've just
chosen and simply copy the information from specific columns on that row to
designated cells in your 'form' on Sheet1.
Here's code I think you'll be able to modify easily enough. It goes into
the data sheet's code segment - so put it there, select the data sheet
(Sheet2) and 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 module
that's presented to you. Edit the 2 sheet names as required, and add more
data movement statements as needed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'change these sheet names as required
Const formSheetName = "Sheet1"
Const dataSheetName = "Sheet2"
Dim formSheet As Worksheet
Dim dataSheet As Worksheet
If Target.Rows.Count > 1 Then
'quit if more than 1 row is involved in the selection
Exit Sub
End If
'move the information from Sheet2 over to Sheet1
Set formSheet = ThisWorkbook.Worksheets(formSheetName)
Set dataSheet = ActiveSheet ' this sheet
'move the information from Sheet2 over to Sheet1
'add more code and change source (data sheet) column references
'and destination (form sheet) cell addresses as required
'
'move data from current row, Column A
'to form sheet cell B2
formSheet.Range("B2") = dataSheet.Range("A" & Target.Row)
'move data from current row, column B
'to form sheet cell A3
formSheet.Range("A3") = dataSheet.Range("B" & Target.Row)
'move data from current row, column C
'to form sheet cell D4
formSheet.Range("D4") = dataSheet.Range("C" & Target.Row)
'continue this for as many cells as you need to deal with

'when you're finished with the data moving...
'cleanup and release resources back to the system
Set formSheet = Nothing
Set dataSheet = Nothing
End Sub

I have created a form on worksheet 1....I want to enter the data on worksheet
2 to automatically populate on the form in worksheet 1... but keep a running
tally of the data in worksheet 2 which is the raw material..... example: in
worksheet 2 column a1, would be the customer first name, column a2 would the
customer phone number, column a3 would be the customer address, column a4
would be the customer city, state, zip..... as i enter the raw data from
worksheet 2 to automatically populate into worksheet 1 (which I did - just
fine) but now my problem is keeping the data in the ROWS column example: row
1 - customer name, customer phone, customer address, customer city state zip
(across the top rows) to display in a spreadsheet format.... so that I
can see that I have completed this form for this customer... thank you...
Please let me know if you can assist me... I know that the form (worksheet
1) will change as the data from (worksheet 2) is enter, and that is fine....
i just want the data stay in the worksheet 2 as the spreadsheet.... so in the
future, I can go to row 115 and see the data entered... I know someone knows
how this is completed..

 

[JLatham]

Can you send me a copy of the workbook - we may have some misunderstanding of
some of the terms, such as "form" which are easiest to clear up by my seeing
the workbook. If you can, attach to an email to (remove spaces)
Help From @ jlathamsite. com

Hi JLatham:
I have tried this code made some modifications... it still did not work for
me... however, I want to thank you very much, because of this, I am looking,
exploring, learning VISUAL BASICS..... I had no knowledge on this matter. I
can't get the second (and the rest) of the column to stay on the spreadsheet
while the form change the information. I will keep working on it, unless you
know what I am doing wrong?
thank you again.
July
*************************************************************

If I understand this correctly, I believe there are at least 2 ways to arrive
at a solution.

First, lets see if I understand correctly: on Sheet2 you intend to enter
customer information in rows across the sheet. When you have a particular
row selected (or a cell within that row), you want to see that row's
information in your form on Sheet1. Is that pretty much it?

Both of my solutions involve some VBA code (a macro) - specifically code
associated with Sheet2 and it's _SelectionChange event processor. Since I
don't see anything in your data on Sheet 2 that readily translates to a truly
unique identifier, like a Customer Number (although a phone number might be
unique, but can't be guaranteed to be), the solution I'd take would be to
have the _SelectionChange() event pick up on the row number you've just
chosen and simply copy the information from specific columns on that row to
designated cells in your 'form' on Sheet1.
Here's code I think you'll be able to modify easily enough. It goes into
the data sheet's code segment - so put it there, select the data sheet
(Sheet2) and 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 module
that's presented to you. Edit the 2 sheet names as required, and add more
data movement statements as needed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'change these sheet names as required
Const formSheetName = "Sheet1"
Const dataSheetName = "Sheet2"
Dim formSheet As Worksheet
Dim dataSheet As Worksheet
If Target.Rows.Count > 1 Then
'quit if more than 1 row is involved in the selection
Exit Sub
End If
'move the information from Sheet2 over to Sheet1
Set formSheet = ThisWorkbook.Worksheets(formSheetName)
Set dataSheet = ActiveSheet ' this sheet
'move the information from Sheet2 over to Sheet1
'add more code and change source (data sheet) column references
'and destination (form sheet) cell addresses as required
'
'move data from current row, Column A
'to form sheet cell B2
formSheet.Range("B2") = dataSheet.Range("A" & Target.Row)
'move data from current row, column B
'to form sheet cell A3
formSheet.Range("A3") = dataSheet.Range("B" & Target.Row)
'move data from current row, column C
'to form sheet cell D4
formSheet.Range("D4") = dataSheet.Range("C" & Target.Row)
'continue this for as many cells as you need to deal with

'when you're finished with the data moving...
'cleanup and release resources back to the system
Set formSheet = Nothing
Set dataSheet = Nothing
End Sub

I have created a form on worksheet 1....I want to enter the data on worksheet
2 to automatically populate on the form in worksheet 1... but keep a running
tally of the data in worksheet 2 which is the raw material..... example: in
worksheet 2 column a1, would be the customer first name, column a2 would the
customer phone number, column a3 would be the customer address, column a4
would be the customer city, state, zip..... as i enter the raw data from
worksheet 2 to automatically populate into worksheet 1 (which I did - just
fine) but now my problem is keeping the data in the ROWS column example: row
1 - customer name, customer phone, customer address, customer city state zip
(across the top rows) to display in a spreadsheet format.... so that I
can see that I have completed this form for this customer... thank you...
Please let me know if you can assist me... I know that the form (worksheet
1) will change as the data from (worksheet 2) is enter, and that is fine....
i just want the data stay in the worksheet 2 as the spreadsheet.... so in the
future, I can go to row 115 and see the data entered... I know someone knows
how this is completed..