consolidating multiple Sheets into a master tab

Hey,

I am trying to consolidate specified ranges in multiple sheets. I would need Marco to copy (f14:i23) for each sheet in a workbook and paste it in to new sheet preferably side by side.



I have attached a excel file that has multiple tabs and within the spreadsheet there is a sheet called output, this is were I would like to see the data copied from each sheet.


also is there a marco to name each tab depending on a cell value. For example in the attached spreadsheet the first tab is called medical device but I would like to change the name to what's in cell value b3 for each sheet. Is this possible ?

Any help is appreciated.