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

 

[ezonemy]

I think you are right. But to reconfirm does your master worksheet contains multiple sheet for each employee? Data from fred and bill's own workbook should go into master worksheet according to their own sheet in master workbook.

If its the same then we are on same track.

 

[oldbrewer]

I thought the master needed to recall all data on one sheet - however - if this is not the case my solution still works eg

in A1 sheetfred in master workbook put ="employeeworkbook":sheetfred!A1 or very similar check syntax

 

[ezonemy]

I thought the master needed to recall all data on one sheet - however - if this is not the case my solution still works eg

in A1 sheetfred in master workbook put ="employeeworkbook":sheetfred!A1 or very similar check syntax

this does not work.

e.g.

Worksheet 1 - contains 1 sheet only where staff choose their name from drop down menu and key in the date and score generated via acitvies they do daily. Each staff end off the day send the sheet to me and i compile them into Worksheet 2 which contains multiple sheet for each staff.

FRED on 16/12/2014 obtained 80% this need to be transfered by macro from Worksheet 1 to Worksheet 2 under sheet named FRED. The score 80% will be parked next to date 16/12/2014 in FRED's worksheet in Worksheet 2.

This is the case where data (DAILY SCORE) from Worksheet 1(template) send to multiple worksheet based on criteria NAME & DATE in their respective sheet.

 

[oldbrewer]

I have just tested this

in workbook bob12345.xls cell A1 type =[fred12345.xls]sheet1!$A$1

this brings across whatever is in A1 of fred12345.xls and if that is updated by fred, the next time you open bob12345.xls you will see the updated value

so in your master sheet you can have dates across the columns and the names in a list in columnA

"Each staff end off the day send the sheet to me and i compile them into Worksheet 2 which contains multiple sheet for each staff. "

Why do you need a sheet for each member of staff inyour master sheet ?

 

[ezonemy]

I think we r off track again. Individual sheet is the one staff need to key in daily to obtain score for the day. This sheet is a template n shared by all staff but due to privacy only one sheet available. End of this sheet there will b a command button where end of the day staff will click this button and macro do its job. A1 in this sheet contains name under drop down menu where all staffs name are there. They just need to select their name, fill the date and score will generated from others they key in.

On the other hand another master worksheet contain multiple sheet based on staff name is kept in shared directory. Staffs are not allowed to open this worksheet.

Macro from individual sheet will copy the daily score on specific staff sheet based on date there n paste the score next to the date.

Is there a way to send my worksheets to you for better understanding?

 

[oldbrewer]

SAY individual sheet has 4 sheets for tom, bill, dave and fred - they all fill in their sheets every day

your master sheet can pull the daily data across automatically whether all onto one sheet or onto 4 individual sheets

would this work for you - automatic data collection ?

 

[oldbrewer]

if you want PM me with your Email, I give you mine, and you send it over as .xls NOT .xlsx

 

[ezonemy]

SAY individual sheet has 4 sheets for tom, bill, dave and fred - they all fill in their sheets every day

your master sheet can pull the daily data across automatically whether all onto one sheet or onto 4 individual sheets

would this work for you - automatic data collection ?

my individual sheet cannot have multiple sheets. It only have 1 sheet where staff are to key in their name in a cell. Will PM u my email

 

[ezonemy]

this the code i got from other its working like i want but, it requires both worksheet to be opened. My Master worksheet should be closed and not to be viewed by staff

Sub Copydata()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim foundDate As Range
    Dim ws As Worksheet
    For Each ws In Workbooks("MASTER.xlsx").Sheets
        If ws.Name = ActiveWorkbook.ActiveSheet.Range("B2").Value Then
            Set foundDate = ws.Range("A:A").Find(ActiveWorkbook.ActiveSheet.Range("E2"), LookIn:=xlValues, lookat:=xlWhole)
            If Not foundDate Is Nothing Then
                foundDate.Offset(0, 1) = ActiveWorkbook.ActiveSheet.Range("H2")
            End If
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

how to mix the above code with the below code

Sub CopynPasteWrkBk()
Dim OutputFile As Workbook
Dim Inputpath As String
Dim Outputpath As String

Set InputFile = ActiveWorkbook
Set OutputFile = Workbooks.Open("J:\4-Admin\Master.xlsx")

InputFile.Sheets("KPI SCORE SHEET 1").Activate
InputFile.Sheets("KPI SCORE SHEET 1").Range("A86", "E86").Copy

OutputFile.Sheets("Prabu").Activate
OutputFile.Sheets("Prabu").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
 
OutputFile.Close savechanges:=True

MsgBox "Data Successfully Logged"

End Sub