Timesheets!

[jemimajean]

hi everyone, hope you can help - has been way WAY too long since I worked regularly with excel so I'm totally clueless here!

Our employees fill in a weekly timesheet and at the moment it's manually copied from those files into a central control. I'd like to have the employees 'submit' it and the individual times load automatically into the central file. I have the central file and the timesheets done prepared, but I can't load one into the other. There's a different file for every week of timesheets, so VLOOKUPs won't work, and I can't think how macros could lookup the relevant week in the central file...i'm getting a bit crosseyed now with all my attempts, so was hoping one of you would have a brainwave!

thanks so much for all your help,
jj

 

[GlennUK]

If you are manually copying the data, then surely you just need a macro to do exactly the same?

 

[jemimajean]

Thanks Glenn, the problem is that the source file (the time sheet itself) is a different file every week - i.e. weeks 1-52 as well as a different one for each employee, so i'm not sure how to have a macro copy the hours, look up the correct week and paste it in the correct line in the central file, for each individual. I'm sure it really is simply I just can't get my brain to work out the "paste it in the correct line" part! A second problem is that the central file can't be visible to the employees so needs to be locked so I'm not sure how to have a macro load it with data without the individual seeing the full file! I'd really appreciate any help at all,
thanks a million,
jj

 

[GlennUK]

For the first part, looking up the correct week position in the central file in VBA to decide where to paste the data sounds like it would be a simple thing. I guess it would depend on how the week number is identified in the workbook being copied, and how it's identified in each row of the central file.

As for your second question:
your file should have an Open password on it, so that unauthorised people can't browse it easily. And processing ( updating ) should only be performed by people with a password.