So this is theoretical at this moment in time, and I was wondering if what I want to do is possible, and how to even start.
I have a performance monitoring worksheet that individual staff process. I am working on a linked manager form that will allow team leaders to monitor and update performance. I've typed a workflow that I envision the vba following below and have attached a sample sheet of the individual form (I've used my own data) and two example manager forms. One that does part of the workflow, and one that if possible I would like it to look like. - file name reflects which is which.
WorkFlow
1. Search in a directory for all files - the only files will be each team member's individual stats
2. Copy the information from the input tab for the current month from each member workbook, and paste them in team member labelled rows in the manager workbook in the relevant month.
3. Allow the manager to change the data/fill in blanks if necessary and then submit this so it is pasted back to the forms where it was taken from.
On paper it sounds so simple, but I have no idea how or if VBA I capable of storing what cells were copied from where and sending updated information back to them.
If it outside of the realms of possibility let me know, having not being able to find any similar posts online I'm assuming it's probably not possible/nobody has felt it is worth it.
Attachments:
KidZest - individual stat sheet; This is the working team member form that allows them to complete monthly and 6 monthly reviews, and save year to date stats. (As far as I'm aware everything is working fine)
Manager - choose direcetory and copy full sheet to tab; This has the directory search function that I would like to use, unless anyone wants to set a userform to select the folder. Currently it copies the full 121 tab from the individual workbooks and updates each time the script is ran. The issue with this is that if team member move their stats will effect the managers full rating when it should take into consideration the period they have been under the manager.
Target Manager Sheet; This still needs some work as it would be best for the different areas to mirror the individual sheets, and the rating formulas need tweaking (I can do this) but if possible I would like the manager form to look as close to this as possible - the charts at the bottom of each sheet are defunct and can go.
I forgot to unprotect the individual sheet, if you need to the password for now is "Toslpass2"
Powers that be in the world of Excel and VBA, can what I'm asking be done or am I chasing a dream that will never happen?
I appreciate any and all help that can be given.
Bookmarks