We help IT Professionals succeed at work.

manual process automation

Last Modified: 2017-05-08

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.

Thanks a lot!
Watch Question

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

I think you should open a project as this is not a question.
Serena HsiMarketing Consultant

What happens when you do these steps and you use 'record a macro' ?
Managing Director/Excel VBA Developer
Distinguished Expert 2018
Unlock this solution and get a sample of our free trial.
(No credit card required)


Awesome job!
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

You're Welcome Steve! Glad I was able to help :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.