Cómo optimizar macros

Question

Cada año tengo un nuevo archivo Excel anual que tiene la misma estructura que el año anterior. El año anterior he definido filas y columnas que tengo que leer de todas las hojas de ese archivo Excel, y devuelvo el resultado del cálculo a ese mismo archivo.

En el nuevo año necesito repetir todo el procedimiento. ¿Existe alguna manera de mover enlaces y fórmulas del año anterior al año siguiente sin volver a vincular los datos manualmente?

Esta es la Macro grabada:

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

¿Cómo hacerlo para todas las hojas?

1. Para todas las hojas con el mismo nombre en ambos archivos
2. encontrar celdas donde está la referencia
3. copiar la referencia del archivo de origen al archivo de destino en la misma ubicación

Answer 1

Esto supone que tenemos las mismas hojas.(con los mismos nombres) enamboslibros de trabajo. Definimos algunos objetos y usamos un bucle:

Sub NewYearData()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim r1 As Range, r2 As Range
    Dim addy As String, sh As Worksheet
    Dim shName As String
    
    Set wb1 = Workbooks("PreviousYear.xlsm")
    Set wb2 = Workbooks("CurrentYear2.xlsx")
    addy = "C16:AH18"
    
    For Each sh In wb1.Sheets
        shName = sh.Name
        Set r1 = sh.Range(addy)
        Set r2 = wb2.Sheets(shName).Range(addy)
        r1.Copy r2
    Next sh
    
End Sub