Transpose Data and Paste Columns Based on Date

[OilEconomist]

Thanks in advance for any suggestions as I will provide feedback for them.

How can I copy data from one tab and transpose, paste special values into another tab matching the date. Details are as follows.

I copy a template "TEM.CAT.PHD" and paste it before another tab "END.PHD.CFP". I then rename the new tab partially based on the first three letters of a tab which is located after "START.DTA.PHD". I do this for the total and then for six possible oil reserve categories (if they exist, but the total will always exist).

So the following code makes the first tab, which is the total and will be named "TNM.TOT.CFP". As in this case, the tab after "START.DTA.PHD" is named "TNM.ALL.PHD". This part of the code was developed thanks to "Fluff".

https://www.mrexcel.com/forum/excel...me-based-first-three-letters-another-tab.html

Sub OilEconomist()
   Sheets("TEM.CAT.PHD").Copy Sheets("END.PHD.CFP")
   ActiveSheet.Name = Left(Sheets(Sheets("START.DTA.PHD").Index + 1).Name, 3) & ".TOT.CFP"
End Sub

I am doing this in pieces so I can try to learn, code parts of it myself, and not to overwhelm anyone who is responding.

The next step is go back to the tab after "START.DTA.PHD" which in this case is named “TNM.ALL.PHD”. The first three letters change depending on the area.

Within "START.DTA.PHD":
Dates start in cell A5 and end at some point with the second to the last entry in column A being “Rem.” The dates are sequential and take the format “1/1/2019”, “2/1/2019”, ……..”8/1/2019”……12/1/2068…..”Rem.”.

• I want to copy columns B, C, and D, from the first date through “Rem.” and in this case paste it into Rows 18, 19, and 20.
• The column has to match up with the dates between the two tabs. The dates are in row 2 of the “TNM.ALL.CFP” and start in column H.
• So if H2 is 1/1/18, columns B, C, and D from "START.DTA.PHD" need to be pasted into rows 18 – 20 starting in column T. Actually if Column T is the matching date column, cell T18 can be selected and paste special values.
• The start dates on each of the two tabs will vary, but “TNM.TOT.CFP.” will always have a start date equal to or before the start date on “TNM.ALL.PHD”.

Please remember the first three letters in this case are “TNM”, but will change where the tab will always be located after “START.DTA.PHD”.