Copy Paste cells into a new sheet based on criteria macro

Hi,

I'm using excel to format the results I get from Survey Monkey. In Survey Monkey I ask the person to identify who they are and then they are asked questions which are relevant to them. Each person has different questions and a different number of questions. The file I download from Survey Monkey has a column with the survey peoples names in it (say column A) under the heading 'Please identify the part of the business you represent.' and then has the questions in row 1, one persons after another. Then, each persons response is recorded in the row which correlates to their name and comes under their question heading. I've attached an example of how this looks in excel in this thread.

What I need to do is have a macro which takes the persons name (what comes under the heading 'Please identify the part of the business you represent.'), puts it into a new worksheet, and then takes the questions which relate to them and put the question and their response one under the other. So each persons responses will be on a new worksheet. What this should look like is on sheet2 of the attached file. I need a macro to do this as I'll be having a new survey to process every month. Also, there is the possibility that I will need to add questions or take some out, so if the macro can search for the responses and number of questions for each person, then it will work.

Thanks.

Arlu1201,

Every month there is a survey but the questions could change from month to month. So I will only run the macro once for each month with whatever questions I've asked that time. Then, the next month I'll do the survey and run the macro on the results. However, I might have had to change a question or two.

JapanDave,

Thanks for the code, it works really well until I put a paragraph of writing into the 'Rational' column. That column on the survey is for the person to give a bit of an explination as to why they've answered the previous questions they way the have. When I run the macro it stops at this line -

wsOut.Cells(1, 1).Resize(x - 1, 2).Value = Application.WorksheetFunction.Transpose(cl)

I'm not sure why, is it to do with the length of text, or punctuation, symbles or something?

Can you make it so that it wraps the text of the 'Rational' column? Would that fix it?

Thank you for your replies JapanDave. Unfortunetly it still gets stuck at that same line. I've attached your macro file below and I've put a very long response into the rational column. If you run the macro you will see that it stops at the line.Excel Problem.xlsm