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_SessionSurvey_Analysis (003)".
Please open "Copy of GRC2016_Session Rating Report (002)" workbook. There will be only one spreadsheet called "Survey Report".
Please open "Copy of CSXNorthAmericaConference_SessionSurvey_Analysis (003)" workbook. There will be a bunch of spreadsheet but you need to only work with the very last one called: "Sheet1".
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.
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.