Collate data from multiple sheets

Hi there, sorry if this has been asked, I am not sure on the phrasing.

We have sent out a very simple excel feedback form. The user picks from dissatisfied through to sattisfied on a variety of questions and the choices are being placed on a hidden 'answers' sheet in row B

So far we have had 120 responses and I am in the process of opening each excel sheet, unhiding the answer sheet, copying row B, going into another sheet for collation and pasting values in the next line down.

I occurs to me there must be an easier way, as I am expecting hundreds more. The problem is the excel sheet is coming in on email so most of them will be identically named. I wondered if there was a way in the collation sheet to look at currently open documents, copy the line a from the answrs sheet and paste it into the next empty line on the collation sheet. I would then make sure I only had the next feedback open in excel, Once done, close that and open the next one.

Could someone help me out on this?

Many thanks, Gary

I have recorded macros before but not to be used of multiple sheets and its here I am a little confused.

Presumbly I would fit the macro into the collation file then hit run, but how will I tell it to pink up the other open sheet?

I will record it and send it on up.