Transpose Data and Paste Columns Based on Date

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
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

Code:
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”.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Watch MrExcel Video

Forum statistics

Threads
1,122,867
Messages
5,598,542
Members
414,245
Latest member
allyciv

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top