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

Hello everyone,

I'm trying to add something to a spreadsheet I've created. I want users to add their data to a worksheet and then use a submit button to send their results to another worksheet. The complex part is that this spreadsheet will be completed numerous times by numerous people, so every time 'Submit' is pressed, I'd like a new row to be added to the target worksheet to record the results, creating an overall table with everyone's results.

I'd imagine that this query has been tackled previously, but I've had a good look around and I can't find anything that quite addresses my particular query. If someone could confirm if this is possible and offer any assistance on the macro code I'd really appreciate it. Please let me know if there's any further information I could provide.

Regards,

Can I assume that each user will add his/her data on a single row?

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,

That's great Mumps1! Thanks!

Please could you advise on how to adapt the code slightly? Sheet1 also contains data in cells C1:C2; is it possible to incorporate this range into the code so that it pastes immediately after the results of B1:B5 in sheet2, i.e. in cells A2:G2 in sheet2? I've tried amending the range to B1:C2, but this pastes the information across two rows. It would also be great to have the values pasted instead of any formulae.

Sorry for not being more specific initially.

Regards,

How about this:

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

Fantastic! Thank you so much for your help!

My pleasure.