Copy Range from Form, and Paste into Workbook (preferably closed)

I have a macro-enabled form that 开发者_开发百科is intended to allow of a team of data-entry staff to record their daily efforts by transaction number. As forms go, it's pretty basic. However, I need to write a macro for a button that will let each person submit their records to a master sheet at the end of their shifts. I need to copy the range data and paste to a master workbook, with the person's name and the date being added to the individual rows.

I'm not sure how to facilitate the copy and paste to a closed workbook, or how to prevent problems with multiple people submitting to the form at the same time. Can anyone offer any suggestions please?

Make it update a database and then generate the report at the end of the day from that. I would also recommend that it inserts into the database each time the user inputs a record so that in the case of a power outage, all of their work from the day doesn't get lost. This will likely reduce the amount of concurrency issues too as the users will be periodically adding records instead of many records at the same time. Search 'VBA DAO' or 'VBA ADODB' to find examples on how to connect to a database with VBA.

You can do this simply by opening the workbook, inserting, and then closing the workbook. There is no simple way to insert into a closed workbook. Note, you could keep things hidden if you're trying to hide things from the user.

Add a reference to the "Microsoft Scripting Runtime" to get access to the filesystem and then use a simple file semaphore to control access to the common workbook.

Regarding the closed workbook, you use Application.Workbooks.Open(...) and .Close

Primary Choice would be to send the items to a database. Since thats already ruled out, I would suggest you write the data to a plain old .csv file. This will be easier to implement, and will not be limited by excel row limits.