LinkBack URL
About LinkBacks
Thanks for all your help and support
Thanks for all your help and support
Last edited by prkhan56; 05-03-2016 at 02:35 PM.
I wasn't quite sure what you wanted to do with the other staff members. Are they each to be written to a separate staff file? Does the cell "mapping" from master to staff change by staff member? This code could be modified to loop through the staff members. Just tell us how you would like to handle that.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Hi,
Please see my reply under your queries.
I wasn't quite sure what you wanted to do with the other staff members.
As mentioned in my original post there is a folder C:\Test where there is a workbook for each Staff Member the names are same as shown in the Master File.
Are they each to be written to a separate staff file?
Yes please, each Staff details from Master should be written to separate staff file.
Does the cell "mapping" from master to staff change by staff member?
Yes it does, the sample I posted was only for Staff number 7 (which I mentioned as Staff 10, apologies for the error on my part)
This code could be modified to loop through the staff members. Just tell us how you would like to handle that.
I would like to run the Macro from the Control Panel made by you.
The Page Path value in B2 Cell would be same in the Control Panel Sheet
In B3 (Staff Page File) I can create a data validation to show the names of the Staff in C3 for Months.
When I click the CopyStaff button. It should go and write the value of that particular staff in its respective workbooks shown in B3 and C3
Hope this is clear.
I am also attaching a file with colors to explain further.
Thanks for your time and support
Hi Dflak,
Any update on my problem please.
Thanks
I need to know one more thing: are you doing this annually or once a month? In other words, do I have to loop through each month and fill in 12 pages, or do I find the current (or last) month and fill on only that page?
I am making the assumption that you want the output to be named like: Staff_StaffMemberName.xlsx.
Hi,
I need to know one more thing: are you doing this annually or once a month?
I will do once in a month
In other words, do I have to loop through each month and fill in 12 pages, or do I find the current (or last) month and fill on only that page?
You don't have to loop to all months. The data needs to be filled in each Staff file for that month. viz the current month
I am making the assumption that you want the output to be named like: Staff_StaffMemberName.xlsx.
The Folder C:\Test already has got files for each Staff1.xlsx, Staff2.xlsx, Staff3.xlsx and so on. The staff names are shown in Column A of the Master file. The macro should go to each Staff file and fill data from Master.xlsx shown against that Staff name
Hope this is clear
Thanks once again for your time.
Here is my latest shot. It creates files Staff1, Staff2, etc. If the file does not exist, it creates it by copying in the blank "template," Staff Group.xlsx.
Last edited by dflak; 04-26-2016 at 03:48 PM. Reason: Forgot Attachment
I will test and revert
Thanks for your time and help
Hi,
One small problem when I was conducting the test.
There are also some blanks in Col A where the Staff are either on leave or left.
So would you please amend the code to cater for any blanks encountered in Col A starting from A4 to A38
So that it would run for all the Staff names in Col A and skip the blanks.
thanks once again for all your time and help
Hi dflak
Is there any solution to the blanks in Col A please?
Thanks for your time
Just got back from vacation. I will have to take a look. It's not a big deal but I'll have to "get to it."
Hope you had a nice time during vacation.
I will wait for your reply
I added a module that contains my standard last row lookup routine and I went down the list of Staff ignoring those that were blank.
Hi,
Thanks the current module ignore blanks but when I tested on my actual data I found couple of issues, if you could rectify them for me please.
1) See image 'sheet name' - my sheet name has month and year eg: Jan 16, Feb 16 and so on
2) If I try to put in Data Validation Jan 16, Feb 16, ....till Dec 16 it gets converted to 16-Jan, 16-Feb ....16-Dec.. see image 'Data Validation'
3) If I run the macro then it comes with an error.. see image 'Error1'
I tried to make the format mmm-yy etc but did not succeed.
Thanks once again for all the time and support.
Sorry forgot to attached the images.
Here they are
Your data validation should match what your tab names are and they should be a string, not a date. Either remove the data validation and type in the value manually or you can compute the tab name using =Text(Date(Year(Today()),Month(Today())-1,1),"mmm yy") assuming that you run the report after the first of the month for the previous month.
The error you are showing is probably caused because you no longer have the name Month_Name defined. Go to Formulas -> Name Manager and see what it is pointing to. It should reference the cell with the tab name.
The tab names in the master book should match the tab names in the staff books. That is an assumption I made.
THANKS THANKS THANKS
It is working now
Thread also marked SOLVED
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks