Need a Macro to Transpose and Combine like Values from rows to columns

I am working with a spreadsheet in which I calculate via macro a value (RRT) based off of RT values and transform & trim the data to only have what I need on a tab (Reformat Data). I then created a pivot table to do the transposing from rows to columns but the user needs to be able to manipulate the data values as needed. They may need to move or delete values (+/- 0.05 difference apart) within the table which the pivot will not allow. So I am looking for a macro that will do the same as the pivot table (combine all like values and transpose the data). This macro will make 1 row for each sample name within each vial and display all the area values under their corresponding RRT value, but this macro table will allow deletions and moving of the values within the table. I am attaching a sample file where I have the reformat data tab that displays the data in rows and columns vertically and the pivot table that has the data the way I need. Can someone please help me with this as I am very new to VBA and not sure how to accomplish it.

Thank you in advance!

OMG! Wow!!! I'm glad my work didn't go to waste. I'm excited to try this out myself. Where is it that I set the dynamic range? Do I dim that at the beginning of the macro too or somewhere in the worksheet? I can't seem to find it in your sample.

Thanks

I found where it is. Now my question is does this have to be changed for every pivot table. The files can be up to 6,000-8,000 rows long so will this formula capture that?

Thanks

OK going to give it a try now and will let you know. Thanks soo much for the quick response!!!

OK so I'm getting an error and can't get the macro to work on my sample workbook. I have gone into Formulas>Name Manager and created a new list named Pivot_Range and copy/pasted your offset formula in the refers to box area. I added your code into the VBA area and created the new sheet Working Data. When I run the macro I get a Run time error '1004': Mehtod 'Range' of object '_Global' failed. What am I doing wrong?

I think it's something with the pivot range because I don't see the outline with a marquee.

I'm attaching the sample worksheet I was working with, please check and let me know what I actually need to do to fix it. I have the real template I need to update with that is 8000 rows long.

I wanted to also ask I am trying to create a button that the user will press when they initially load the data into a blank template and it will step through all the macros I have to get to the end results that they want. If I add this piece of code at the end will it work ok once we fix this issue?

It works!! Thank you soo much.