Need help on copy selected cell into closed workbook

[ezonemy]

hi,

i have 2 workbook.
Wb 1 named as SCORE SHEET, contains 1 sheet where each staff will key is to key in their daily activities and there will b percentage given end off the day. Each staff requires to open this sheet, select their name, select the date /month / year then proceed to key in their data.

Wb 2 named as MASTER stored in shared drive and locked for other staff. This wb contains multiple sheets which is the staff names. All the daily data from each staff to be keyed in accordingly.

Currently all staff will save a copy and send WB1 via email and manually i have to key in data into WB2.

I hope there is macro to be set in WB1 where data from selected cells to be copied in to WB2 without opening WB2 and according to staff name and date.

Example;
WB1 (SCORE SHEET)
A1 - Staff name (John,Mary...) (there will drop down menu)
C1 - Date (dd/mm/yyyy)
J1 - Daily score (70%,30%...)


WB2 (MASTER) (contains +-50 sheets which each is named after a staff)
A1 - Staff name (John,Mary...) (fixed in each sheet according to staff)
A2 - date (dd/mm/yyyy - e.g. 01/05/2014)
A3- date (e.g. 02/05/2014) and for rest of the year.
D2 - Daily percentage

(e.g. staff A got 79% on 01/05/2014 so in WB2 sheet named staff A contains A1=staff A, A2=01/05/2014 and D2=79%)

I need a macro placed WB1 where at end off the day the staff just need to press the button and the value from WB1 A1,C1,D1,E1 & J1 copied to WB 2 into correct cells for whole year.

Hope the above clarifies

 

[oldbrewer]

in workbook2 - the master - in sheet fred cell A1 enter = workbook1:fred.xls1$A$1

now when you open workbook 2 it will contain the latest update to A1 in workbook1 sheet fred

 

[ezonemy]

dear oldbrewer, i dont think this is the solution. the macro i need to match data in WB1 and WB2 and fill the data in the correct cell from WB1. The macro shud match satff name & date from WB1 to WB2 then fill the daily score which the staff get for that day in correct cell next to the date in WB2 in the staff's sheet.

 

[oldbrewer]

if the design of each staff members worksheet is the same, ie date and score in A1,A2 then B1,B2 etc

then in your master workbook row 1 will transfer across fred info and row 2 james info etc etc
always picking up any data entered - future dates and scores will be blanks so blanks will be in the master

 

[ezonemy]

if the design of each staff members worksheet is the same, ie date and score in A1,A2 then B1,B2 etc

then in your master workbook row 1 will transfer across fred info and row 2 james info etc etc
always picking up any data entered - future dates and scores will be blanks so blanks will be in the master

WB1 is common workbook with only 1 sheet. Staff name, date and daily score value will change everyday

 

[oldbrewer]

yes, WB1 will pull over new additional information every day. Am I not grasping what you want ?

 

[ezonemy]

yes, WB1 will pull over new additional information every day. Am I not grasping what you want ?

yes, WB1 is where my staff will use to key in their daily activities and end off the day the score need to transferred to WB2. Macro need to match name from WB1 then date and paste the daily score next to the date in WB2.

 

[oldbrewer]

	master
	fred	bill										employees
01/12/2014	22	111
02/12/2014	33	222
03/12/2014	44	333
04/12/2014	55	444							fred sheet						billsheet
05/12/2014	66	555
06/12/2014	77	666
07/12/2014	88	777
08/12/2014	99	888						sales							sales
09/12/2014	0	0					01/12/2014	22						01/12/2014	111
10/12/2014	0	0					02/12/2014	33						02/12/2014	222
11/12/2014	0	0					03/12/2014	44						03/12/2014	333
							04/12/2014	55						04/12/2014	444
							05/12/2014	66						05/12/2014	555
							06/12/2014	77						06/12/2014	666
							07/12/2014	88						07/12/2014	777
							08/12/2014	99						08/12/2014	888
							09/12/2014							09/12/2014
							10/12/2014							10/12/2014
							11/12/2014							11/12/2014
	these are on the same sheet for clarity
	but fredsheet and billsheet can be
	different sheets in a separate closed workbook
	please explain if you want something different
	if say fred is not working for a few weeks
	youcan use iserror and return a n/a

<colgroup><col><col span="6"><col><col span="6"><col><col span="2"></colgroup><tbody>
</tbody>

 

[ezonemy]

dear olbrewer, the master worksheet shud have only 1 sheet. The example above got 2 set of data which belongs to fred & bill. Sorry if im wrong. Master worksheet shud contain 1 staff data only. You are right for Employees worksheet. Please take note the dates in Employees sheet are fixed so the macro just need to match the date and insert the score.

If the staff not working on that date, then the score for that date will be blank.

 

[oldbrewer]

my master worksheet only has dates, names of staff, and sales figures. These are automatically pulled from fred and bill's own workbooks. I only put fred and bill on the same sheet so that you could see what data I was pulling.

I still suspect that you want something different.........