If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel Spreadsheet
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.. |
#2
|
|||
|
|||
Excel Spreadsheet
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" wrote: 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.. |
#3
|
|||
|
|||
Excel Spreadsheet
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 ************************************************** *********** "JLatham" wrote: 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" wrote: 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.. |
#4
|
|||
|
|||
Excel Spreadsheet
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 "july" wrote: 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 ************************************************** *********** "JLatham" wrote: 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" wrote: 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.. |
Thread Tools | |
Display Modes | |
|
|