Ben Mac
08-07-2017, 10:18 PM
OK, So I have a macro enabled worksheet with 2 Macros that make a set of new sheets that can be printed out a forms and signed by the person responsible for seeing that job completed. One Macro creates General actions from a list of Actions in sheet “general” and the other macro creates Minute actions from a list of actions in sheet “minutes”
I would like another Macro that will look at a list of initials in the first sheet (Sheet = Attendance, Row = I) and then search the cell E3 for all the sheets between sheet “general” and sheet “minutes” (these are the general Actions sheets generated by the first macro) and make a new workbook for each person from the list of initials with all the actions (action sheets e.g. “Action 1 General”) assigned to them saved in it.
I would then want the same Macro to look at cell C5 for all the sheets between sheet “minutes” and sheet “signoff” (these are the minute Actions sheets generated by the second macro) and add the actions (action sheets e.g. “Action 1 Minutes”) to the workbook already created for each person from the list on the first sheet.
I would like the Macro to then save these new Workbooks to a directory of my choosing
Points of Note:
The two Macros need to be run manually before this Macro could be run. And are located on the ribbon
This Macro could be a third Button on the ribbon
I would not want a workbook created if there were no actions for anybody on the Initials List on the first page
Lastly, it would be nice if the workbook for each person Initialed could be sent their workbook of actions automatically. So I have included on the first sheet, both their full names in the first column and their email in the last column (J)
I Hope someone can help, I’ve worked my way through the first set of Macros in my workbook, and although I have had ideas of how I could do this, e.g. making a list somewhere of what worksheets are needed to be saved using VLookup or writing a macro to look at the list of Initials and searching cells to find the sheet names of those worksheets it’s not quite coming together in my head.
However, once I had that list I thought I could use the Macro I found on this site (Article id=359 to look) which I could use to look at that list and save them to the new workbooks. Don’t know how I could email them though. That’s beyond me.
Anyway I have attached a small example of my workbook, if anyone can help, that would be much appreciated.
Thanks
Ben
I would like another Macro that will look at a list of initials in the first sheet (Sheet = Attendance, Row = I) and then search the cell E3 for all the sheets between sheet “general” and sheet “minutes” (these are the general Actions sheets generated by the first macro) and make a new workbook for each person from the list of initials with all the actions (action sheets e.g. “Action 1 General”) assigned to them saved in it.
I would then want the same Macro to look at cell C5 for all the sheets between sheet “minutes” and sheet “signoff” (these are the minute Actions sheets generated by the second macro) and add the actions (action sheets e.g. “Action 1 Minutes”) to the workbook already created for each person from the list on the first sheet.
I would like the Macro to then save these new Workbooks to a directory of my choosing
Points of Note:
The two Macros need to be run manually before this Macro could be run. And are located on the ribbon
This Macro could be a third Button on the ribbon
I would not want a workbook created if there were no actions for anybody on the Initials List on the first page
Lastly, it would be nice if the workbook for each person Initialed could be sent their workbook of actions automatically. So I have included on the first sheet, both their full names in the first column and their email in the last column (J)
I Hope someone can help, I’ve worked my way through the first set of Macros in my workbook, and although I have had ideas of how I could do this, e.g. making a list somewhere of what worksheets are needed to be saved using VLookup or writing a macro to look at the list of Initials and searching cells to find the sheet names of those worksheets it’s not quite coming together in my head.
However, once I had that list I thought I could use the Macro I found on this site (Article id=359 to look) which I could use to look at that list and save them to the new workbooks. Don’t know how I could email them though. That’s beyond me.
Anyway I have attached a small example of my workbook, if anyone can help, that would be much appreciated.
Thanks
Ben