I have a data set that has 200 rows with (let's say) 40 entries per row and each entry is divided by year (40 years worth of values). The entries are numbered 1 - 200.

I would like to create a new dataset by entry number and make each yearly entry monthly depending on whatever year it corresponds to back in the original data set (the value for the year is kept consistent throughout the 12 months of that year) on a separate part of the same sheet

I currently have this in excel as an HLookup/match formula referencing the entry number and year but want to automate the process in VBA.

An idea I have is to loop 200 times through and find the entry number and year of the date and to match it with the original table's entry, then copy and paste into a new table.

I'm new to VBA and any help is greatly appreciated. Thanks!!