Formatting Imported Data Help

I have an issue that I do not know how to figure out. I have not really worked with Excel much, but have been asked to find a way to create a macro that formats some information that is imported from a survey. I am attaching a sample spreadsheet from how the data looks when imported. What I am, ultimately, trying to accomplish is to separate each question into its own tab. Each tab should have the question listed in A1 and then have a header called "Name" in column 2 and the listed answer choices provided as headers for the next columns. The data should then be organized showing how each person answered each question. There should also be a "no response" column that populates if the person does not answer a specific question.

With regards to the imported data, the number of questions, answers, and people answering them could change with each imported survey.

Is there a way to create a macro that can do this based on the data provided as an attachment? If so, how would I go about creating said macro?
Any help provided would be greatly appreciated.

Thank you.