Macro to Transpose Data Based on Column Data

Hello,

I need help with a macro I've been trying to write with no success.

I am attaching the sample worksheet.

Basically, I have a table with bunch of data, with a header row on top. What I'd like to do, is take the data from column D as the criteria, and make a worksheet named after the data within D2, D3, D4, etc.

After the sheet is created with that name, I need to copy the data from row 1, and paste transpose it to the new sheet in column A. With that, I also need to copy the data from the row, that D2 was taken from, and paste transpose it to column C as column B is just used for a spacer with a width of 1 (see the tab "B-Sample" in the attachment for an example of what I need it to look like).

I need to do this for all remaining data in column D. So in essence, I would have tabs for Room #s, B1, B2, B3, B4, B5, displaying the "Report".

My real excel file has a row header from A1:DM1.

Can anyone assist with thsi?