I have a workbook with multiple worksheets. On one worksheet is a list of data (data worksheet) with the top horizontal row listing security classes and the vertical left column a series of years (say from 1980 to 2016). On the second worksheet (rank77) I have ranked the highest data value for the year 1977 (row) and assigned the next year’s allocation per some predetermined logic. On a third worksheet (calculations78 worksheet) I have a series of rows corresponding to the classes of securities showing their assigned allocation percentages with formulas in each row. At the lower portion of the worksheet, I arrive at yearly return figure. Now, I want to use VBA code or macros to do this behind the scenes in one sub routine doing all of the years. The steps are the following: rank the returns in the first year, 1977, from the data worksheet, using my logic assign each an allocation, take that allocation to the Calculation worksheet for the next year and also find the returns for each Class for the next year, lastly, allow my formulas to calculate a yearly return. Finally, I carry the return to a Summary worksheet. Then, I want to loop and find a result for the next year. I have a beginner's knowledge of VBA.
Solved by Z. Y. in 11 mins