How to use a Macro submit button to export results to another worksheet

**EscargotMyCargo** · 05-20-2013, 10:46 AM

Hello, thanks for replying.

The worksheet that users will complete will be the same for all so they won't be completing separate rows. For example, users will open the document and add information to cells B1-B5 and then the results of the calculations will appear in cells C1 and C2. I want to find a way to export the information from all aforementioned cells into a target worksheet once the information is 'submitted'.

User 1 opens the workbook, adds data to B1-B5 of worksheet 1, presses submit and the information is exported to row 2 of worksheet 2. The file is saved and closed.
User 2 opens the workbook, adds data to B1-B5 of worksheet 1, presses submit and the information is exported to row 3 of worksheet 2. The file is saved and closed.

Essentially, worksheet 1 works as a portal to obtain the data and everything is saved on worksheet 2. I hope that is clear, sorry I can't share the file, as it contains some sensitive information.

Regards,

**Mumps1** · 05-20-2013, 11:22 AM

Assign this code to a button on your worksheet. It will also clear the range after it is copied to sheet 2 so that it is ready for new input for the next user.

Sub CopyData()
    Range("B1:B5").Copy
    Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
    Range("B1:B5").ClearContents
End Sub