Dynamic copy & paste according to multiple criteria

[ADoc01]

Hi all - I really appreciate the help in advance.

I am stuck with the following problem.

Sheet2 shows a list of prices for a derivative contract for a set of tenures (Jun22, Jul22, Aug22 ... etc.). This data is pulled through from another worksheet via VLOOKUP and INDEX MATCH formulas.

At the end of each day, I want to create a macro that takes these prices from Sheet2 and stores them into Sheet3 once the macro is run. I require the data from Sheet2 to be put into the correct tenure (columns) and to the correct date (rows). The desired output for 20/05/2022 is displayed in Sheet3.

I have previously solved this using a macro to copy and transpose the data, however it is only a short term solution as the tenures are changing as time goes along. For example Jun22 will expire and be made redundant at the end of May and this means that it requires regular checking to ensure the correct data is put into the correct row/column and issues are caused when a contract expires.

Note
1) The format of the data in the tenures column in both Sheet2 and Sheet3 is in text and not as a date. This has to stay this way because of how data is pulled into my worksheets both between different sheets and through external RTD sources and workbooks.
2) The data in Sheet2 changes daily and is taken from various other Sheets in the same workbook via VLOOKUP and INDEX MATCH formulas

Please let me know if further explanation is needed!

[AliGW]

Administrative Note:

Welcome to the forum.

Members will tailor the solutions they offer to the version (NOT a release number like 2020) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[JohnTopley]

Try

in B3

=INDEX(Sheet2!$B$4:$B$12,MATCH($A3,Sheet2!$B$2:$Z$2,0)+MATCH(Sheet3!B$1,Sheet2!$A$4:$A$12,0)-1)

It is not clear how date is put in Sheet3 nor whether there can be more than one date entry on Sheet2

[ADoc01]

Hi John thank you for the response.

Unfortunately the data in Sheet2 is constantly changing and is being pulled in live via an RTD function. I need the cell B3, B4 etc to represent a snapshot of this data when the macro is run and store the data in Sheet3 according to the correct tenure and date.

I am sorry if the explanation above wasn't clear.

[AliGW]

Please update your forum profile, as I requested above. Thanks.

[JohnTopley]

data in Sheet2 is constantly changing

Please show examples of what is changing and how.

The formula will handle changing data.

[ADoc01]

Apologies for not being clear.

In Sheet2 the data for each tenure is pulled from an external source via an RTD Function and is near enough changing every second.

The formula above works, however the following day and going forward I imagine it will start to read N/A as the date in reference is now in the past.

Cell B2 in Sheet2 will have a =Today() formula.

What I require is a snapshot of the values at a certain point everyday such that each value is copied from Sheet2 and pasted into Sheet3 as values under the correct tenure column (Jun22, Jul22 .. etc.) and in the correct date row (which will always be today).

[kvsrinivasamurthy]

Macro code

Please Login or Register  to view this content.

[ADoc01]

kvsrinivasamurthy thank you so much - that has worked for the initial sample.

I am finding an issue when I have to add periods to both Sheet2 and Sheet3 (see attached).

In this example, the Jun22, Jul22 and Aug22 have been made redundant and so have no values. I have added Oct22 Nov22 Dec22 Jan23 Feb23 and Q2 23 to both sheets.

Upon executing the macro, the values for some are correct but other periods are going into incorrect boxes.

Sorry to keep pestering and I really appreciate you taking the time to look at this.

[kvsrinivasamurthy]

Corrected Code:

Please Login or Register  to view this content.

[ADoc01]

kvsrinivasamurthy this is fantastic thank you so much for the help!

I am working through the code to try and learn and understand this for myself so I can apply it in practice now.

Your help is very much appreciated.

[kvsrinivasamurthy]

Welcome. Thanks for feed back.

[ADoc01]

Hi - can I ask one more thing if possible?

Is it ok to provide comments for each step of the above code? I am sorry to be a pest - I need to adapt the solution now to a new sheet which slightly differs in format and really want to work it out for myself!

Thank you again for your help.

[kvsrinivasamurthy]

I have added comments in the code. Pl see file.

[ADoc01]

Hi kvsrinivasamurthy

Can you send the code on here if possible as my work PC cannot allow me to open the file with macros.

Thank you again for your help sir.

[kvsrinivasamurthy]

Code:

Please Login or Register  to view this content.

[ADoc01]

thank you very much for helping to solve my problem!

[kvsrinivasamurthy]

Welcome. Thanks for feedback.