Every year I have a new annual Excel file that has the same structure as the previous year. In the previous year I have defined rows and columns that I have to read from all the sheets of that Excel file, and I return the calculation result to that same file.
At the new year I need to repeat the whole procedure. Is there a way to move links and formulas from the previous year to the next year without manually rebinding the data?
This is recorded Macro:
Sub NewYearData()
' NewYearData Makronaredba
Windows("PreviousYear.xlsm").Activate
Sheets("1.A.1.a.ii Liquid Fuels").Select
Range("C16:AH18").Select
Selection.Copy
Application.WindowState = xlNormal
Windows("CurrentYear2.xlsx").Activate
Range("C16").Select
ActiveSheet.Paste Link:=True
ActiveSheet.Paste
Windows("PreviousYear.xlsm").Activate
Sheets("1.A.1.a.ii Gaseous Fuels Gaseo").Select
Range("C16:AH18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("CurrentYear2.xlsx").Activate
Sheets("1.A.1.a.ii Gaseous Fuels Gaseo").Select
Range("C16:AH18").Select
ActiveSheet.Paste
End Sub
How to make it to for all sheets?
- For all sheets with same name in both file
- find cells where reference is
- copy reference from source file to destination file at same location