Need help on copy selected cell into closed workbook

ezonemy

New Member
Joined
Dec 7, 2014
Messages
19
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

Board Regular
Joined
Apr 11, 2010
Messages
10,985
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

New Member
Joined
Dec 7, 2014
Messages
19
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

Board Regular
Joined
Apr 11, 2010
Messages
10,985
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

New Member
Joined
Dec 7, 2014
Messages
19
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

Board Regular
Joined
Apr 11, 2010
Messages
10,985
yes, WB1 will pull over new additional information every day. Am I not grasping what you want ?
 

ezonemy

New Member
Joined
Dec 7, 2014
Messages
19
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

Board Regular
Joined
Apr 11, 2010
Messages
10,985
master
fredbillemployees
01/12/201422111
02/12/201433222
03/12/201444333
04/12/201455444fred sheetbillsheet
05/12/201466555
06/12/201477666
07/12/201488777
08/12/201499888salessales
09/12/20140001/12/20142201/12/2014111
10/12/20140002/12/20143302/12/2014222
11/12/20140003/12/20144403/12/2014333
04/12/20145504/12/2014444
05/12/20146605/12/2014555
06/12/20147706/12/2014666
07/12/20148807/12/2014777
08/12/20149908/12/2014888
09/12/201409/12/2014
10/12/201410/12/2014
11/12/201411/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

New Member
Joined
Dec 7, 2014
Messages
19
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

Board Regular
Joined
Apr 11, 2010
Messages
10,985
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.........
 

Forum statistics

Threads
1,081,793
Messages
5,361,325
Members
400,627
Latest member
Mcomeaux

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top