Copy cell value from 1 workbook to another workbook based on criteria

hi all, i have 2 workbook. WB1 is named SCORESHEET and WB2 named MASTER. WB1 is template for my staff (+-50 staffs) to log in data daily. It contains only 1 sheet and the name is selected via dropdown button. Date to keyed in daily based on their working day. The score in WB1 will accumulate daily based on data keyed in. The staff are required to save and send this myself.

WB2 contains multiple sheets (+-50) named based on the staff. There is template in all the sheet and it is the same except for the name on each sheet.

I require a macro which copies selected cell value from WB1 into WB2 according to name of the staff, date and the score accordingly. The WB2 will be saved in network but staffs are not allowed to open it.

attached is the workbook sample.Scoresheet.xlsmMASTER.xlsm

Originally Posted by pjwhitfield

Do you have MS Access? and do all staff have access to a single central directory?

If so, then Id recommend creating a quick Access database containing a table to hold this data, then on pressing the button you send the data to the table, the master sheet will then have a script in to pull the data out in whatever format, calculations etc you need.

There are (in my opinion) many benefits to this, firstly its easier to code, you just need setup a connection to the database and pass the relevant data to it. Secondly, its far more powerful to get data out and will hold years and years worth of data without a problem whereas the spreadsheet will soon look overcomplicated if you hold a lot of days.

Finally (whichever method you do), if all staff have a logon to the system then you can collect the staff details automatically, this reduces errors of staff picking the correct name and reduces the number of steps they need to carry out.

add the following to a module:

Please Login or Register  to view this content.

=fnGetPID() will then return their Windows logon name.

Hi unfortunately no MS Access. But we do have central directory where both the workbook can be stored