What does it say on the submit button?

Code to submit data from a survey

• QUESTION: Hey I used some code you had suggested that would use a Submit button to copy responses from cells in a survey (sheet 1) to rows (in sheet 2). Once you click the Submit button, it is suppose to also clear the cells in sheet 1. The next data entered would then get submitted to the next empty row (on Sheet 2). I tried the code and it does not clear the cells on sheet 1, nor put the next data on to the second row. I am using2007, can you help with why the code is not working properly? shelby Private Sub Submit() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, r2 As Range Dim v1 As Variant, v2 As Variant Set sh1 = Worksheets("demographics") Set sh2 = Worksheets("data sheet") ' specify the cells that will contain information in sheet1 v1 = Array("D2", "G5", "H5", "C10", "D12") ' in the same order specify what columns that info would be ' placed in in sheet2 v2 = Array("A", "B", "C", "D", "E") ' find the next open row in sheet2 using column A rw = sh2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row ' Now copy and clear the data For i = LBound(v1) To UBound(v1) Set r1 = sh1.Range(v1(i))(1) Set r2 = sh2.Cells(rw, v2(i)) r1.Copy r2 ' clears the value after it is transferred r1.ClearContents Next i End Sub ANSWER: Shelby, I just tested it again and it worked fine for me. It did all the things you say it doesn't do. I looked through the code and all the commands are there. Since you didn't give me any specific locations, I put in a array of addresses. I used D2, G5, H5, C10, D12. I wrote values in these cells and ran the macro. It put the values in those cells in to A2:E2. It did this because I specified that D2's value should go to column A, G5's value to column B, and so forth. These arrars defined where to find the data and where to place it. ' specify the cells that will contain information in sheet1 v1 = Array("D2", "G5", "H5", "C10", "D12") ' in the same order specify what columns that info would be ' placed in in sheet2 v2 = Array("A", "B", "C", "D", "E") You notice in my code that I used sheet names "demographics" and "data sheet". I can assure you that I didn't make these up, but you told me those names in the original question. If those are not your sheet names, then change them to reflect your sheet names "Sheet1" and "Sheet2" This line of code finds the next available row in "Data Sheet"/"Sheet2" rw = sh2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row it depends on data being in column A. If there will be no data in column A because you don't always fill up all your cells in the entry sheet or you aren't writing your data to column A, then you need to change what column it should use to find the next available rows. This line specifies where to find the data using the above described array (v1) Set r1 = sh1.Range(v1(i))(1) Now if you put an address like B5:D5 in your array, then it would only refer to B5. I will modify that line of code to be Set r1 = sh1.Range(v1(i)) Also, I make no allowance for merged cells. You didn't say anything about merged cells - but merged cells always require special consideration. I have given no special consideration for merged cells since you did not mention anything about having merged cells. This specifies where to paste the data: Set r2 = sh2.Cells(rw, v2(i)) notice that it uses the rw variable which should point to the next available row unless you have empty cells as I stated previously. this actually copies the data r1.Copy r2 this then clears the cell where the data originated ' clears the value after it is transferred r1.ClearContents Here is the code with that single line changed - but doubt that that has any affect on what you are describing. Private Sub Submit() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, r2 As Range Dim v1 As Variant, v2 As Variant Set sh1 = Worksheets("demographics") Set sh2 = Worksheets("data sheet") ' specify the cells that will contain information in sheet1 v1 = Array("D2", "G5", "H5", "C10", "D12") ' in the same order specify what columns that info would be ' placed in in sheet2 v2 = Array("A", "B", "C", "D", "E") ' find the next open row in sheet2 using column A rw = sh2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row ' Now copy and clear the data For i = LBound(v1) To UBound(v1) Set r1 = sh1.Range(v1(i)) Set r2 = sh2.Cells(rw, v2(i)) r1.Copy r2 ' clears the value after it is transferred r1.ClearContents Next i End Sub It still has the locations I put in as examples. I am sure you have put in the actual locations. So I have done the best I can based on what you have told me. If your workbook varies from the assumptions I have made, then not much I can do since I can't see it - you basically have to make any unique adjustments or post back with a description and the specific problem. The code is tested, follows standard practice, and does what I understand you want it to do. I have shown you the lines of code that exist to do each thing you asked. ---------- FOLLOW-UP ---------- QUESTION: I found the error. If you leave the cell selected on sheet 1 that you specify in the code, then it will not copy information to sheet 2.

• Answer:

  Shelby, I can't reproduce that. I selected a cell, I selected all cells in the list and each time it worked fine for me. Tested it over and over. Always worked for me. There is nothing in the code that would lead me to believe that the code would not work if any particular cell were selected. If you are still in edit mode, i.e. you are still editing in the cell, then the code won't run (you can't run the code) - but that is true with any code. However, I don't think that is what you meant. Hopefully you don't have event code running in your sheet module. If you have such code, I can't predict what it might be doing.