Unable to run the "Year change" macro

If your "Change year" macro gives a runtime error or you end up with reference error(s) ("=#REF!") on your "I-main" sheet, you need to remove the references to previous years, before running the macro.

References to previous years

The "Change year" macro deletes the values of the oldest time period and moves all time periods one step towards the oldest.  This removal of the oldest time period causes a problem if you are referencing to these deleted values in your becoming years.

Removing unwanted references

You can avoid the errors by clearing the references to the oldest time period before running the "Change year" macro.

Removing the references:

1. Go to your "I-main" sheet.
2.  Look for references to the previous year in the cells of the second oldest time period.
   
   
3. When you find a cell, which is referencing to the corresponding cell of the previous year, just copy the cell.
4. Paste the cell in itself by using the paste as “Value” option in Excel. This keeps the value, but removes the reference.
   
   
   
5. Repeat this to all the cells which have a reference to the oldest time period.
6. When there are no references left to the first time period, the removal of this column will not affect the remaining figures.

For example if your columns D and E include the figures of two successive years and you are going to run the "Change year" macro:

1. Check the column E for any references to column C.
2. If you find a reference for example to cell D92 in the cell E92, just copy the E92 and paste it as a values into E92 itself.
3. The values in neither E nor D 92 should change, but E92 will not have a reference to D92 left anymore.

Tips

• To avoid the caused problem, try not to use references to the previous year.
• Do the previous steps to other columns as well, not just the second oldest time period. This way you will avoid the problem in the future.