Steve Bez
asked on
manual process automation
Hello,
I need a VBA that can automate the following manual process below.
The input workbook is attached and called "Copy of GRC2016_Session Rating Report (002)".
The output workbook is attached and called: "Copy of CSXNorthAmericaConference_
Please open "Copy of GRC2016_Session Rating Report (002)" workbook. There will be only one spreadsheet called "Survey Report".
Please open "Copy of CSXNorthAmericaConference_
The format of "Sheet1" should remain the same.
The first data entry in Sheet1 is "Ira Winkler" that comes from "Survey Report" spreadsheet Q2. Let's use excel location like Q2, instead of hardcoded names in the code.
The second entry is "Security Awareness: What Compliance Should Look Like". In "Sheet1" it's location is in "P2"
Next entry is the date: location "R2", please use the same format as in "Sheet1".
Next we have questions that are abbriviated as Q1, Q2, etc. (we will use these abbreviations in the graph below). The real names for them come from "Survey Report" spreadsheet, those are the headers. Let me know in case you get confused.
Next...
Session Average. Those numbers come from the first row after the header row from "Survey Report".
Conference Average. For the first question "The stated learning objectives were met", these numbers are calculated like that: select all numbers from the first column and get an average. Do the same for all the other questions.
Overall average. You just take all numbers for session average and calculate and average.
Conference average. Same as with Overall average.
Total Respondants. Comes from "Survey Report' N2.
The graph below should be constructed based on these numbers. See the legend.
Right now the conference average line red line on the graph is not in sync with the numbers but it should be. The blue bars are in sync.
Anyway, for each row from "Survey Report" we need to create a separate spreadsheet within the same workbook and name that spreadsheet with corresponding speaker name. Within that spreadsheet we should build all that data and graph like in the attached example "Sheet1".
Let me know if you have any questions. I know it's a lot of explanations, so I cut out some details that I think are self explanatory. If something is not clear, please let me know.
Thanks a lot!
Copy-of-CSXNorthAmericaConference_S.xlsx
Copy-of-GRC2016_Session-Rating-Repor.xls
I need a VBA that can automate the following manual process below.
The input workbook is attached and called "Copy of GRC2016_Session Rating Report (002)".
The output workbook is attached and called: "Copy of CSXNorthAmericaConference_
Please open "Copy of GRC2016_Session Rating Report (002)" workbook. There will be only one spreadsheet called "Survey Report".
Please open "Copy of CSXNorthAmericaConference_
The format of "Sheet1" should remain the same.
The first data entry in Sheet1 is "Ira Winkler" that comes from "Survey Report" spreadsheet Q2. Let's use excel location like Q2, instead of hardcoded names in the code.
The second entry is "Security Awareness: What Compliance Should Look Like". In "Sheet1" it's location is in "P2"
Next entry is the date: location "R2", please use the same format as in "Sheet1".
Next we have questions that are abbriviated as Q1, Q2, etc. (we will use these abbreviations in the graph below). The real names for them come from "Survey Report" spreadsheet, those are the headers. Let me know in case you get confused.
Next...
Session Average. Those numbers come from the first row after the header row from "Survey Report".
Conference Average. For the first question "The stated learning objectives were met", these numbers are calculated like that: select all numbers from the first column and get an average. Do the same for all the other questions.
Overall average. You just take all numbers for session average and calculate and average.
Conference average. Same as with Overall average.
Total Respondants. Comes from "Survey Report' N2.
The graph below should be constructed based on these numbers. See the legend.
Right now the conference average line red line on the graph is not in sync with the numbers but it should be. The blue bars are in sync.
Anyway, for each row from "Survey Report" we need to create a separate spreadsheet within the same workbook and name that spreadsheet with corresponding speaker name. Within that spreadsheet we should build all that data and graph like in the attached example "Sheet1".
Let me know if you have any questions. I know it's a lot of explanations, so I cut out some details that I think are self explanatory. If something is not clear, please let me know.
Thanks a lot!
Copy-of-CSXNorthAmericaConference_S.xlsx
Copy-of-GRC2016_Session-Rating-Repor.xls
Subodh Tiwari (Neeraj)
I think you should open a project as this is not a question.
What happens when you do these steps and you use 'record a macro' ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome job!
You're Welcome Steve! Glad I was able to help :)