Hi all - I really appreciate the help on this forum as always.
I am stuck with the following problem.
Screenshot of 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!
I am stuck with the following problem.
Screenshot of 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!
