Cell Programming Help Needed

I need some help with programming a cell in Excel. I have attached a sample so you can see what I am working with. What I need is a line of code for cell B1 on Sheet2. What I need it to do is read the name of the sheet that it is on (Sheet2), then go back to Sheet1 and find the row where that sheet name is listed (row 1 on Sheet1 in this case since I need the code for Sheet2). The code then needs to go to column B and the correct row (row 1 in this case) and take the text in that row/column (B1) and insert the text in cell B1 on Sheet2.

Then I will do the same thing for Sheet3-Sheet5. So the code will read the name of each sheet, go to Sheet1 and then go to B3, B4 or B5 and pull the text from those cells into B1 of each respective sheet.

I have only managed to get the cell to read and display the name of the sheet that it is on and I'm not sure what to do from there. Any help would be greatly appreciated.

Thanks,
Doug