Copiar fórmulas de Excel a otra hoja con VBA

Para copiar fórmulas de una hoja de cálculo de Excel a otra con VBA, asigne el rango de origen FormulaR1C1a un rango de destino del mismo tamaño. Esto transfiere el patrón de la fórmula sin copiar el formato de las celdas, y las referencias relativas se ajustan a las celdas de destino. La macro que se muestra a continuación utiliza los nombres de las hojas de cálculo, por lo que no depende de la hoja activa.

Antes de empezar

Utilice la versión de escritorio de Excel; Excel para la web puede abrir un libro de trabajo que contenga macros, pero no puede crear, ejecutar ni editar macros VBA, como se explica en la guía de Microsoft sobre Excel para la web . Confirme los nombres exactos de las pestañas y las direcciones de origen y destino. En este ejemplo, las fórmulas están en Source!C2:C10y se colocarán en Summary, comenzando en C2.

El ejemplo utiliza ThisWorkbook, que se refiere al libro de trabajo donde se almacena el código de la macro. Mantenga la macro en ese libro de trabajo o cambie la referencia del libro de trabajo si el código se encuentra en otro lugar. Los nombres de las hojas de cálculo se seleccionan de la Worksheetscolección del libro de trabajo; el nombre entre comillas debe coincidir exactamente con la pestaña.

Copiar fórmulas a otra hoja con VBA

Abra el Editor de Visual Basic desde la pestaña Desarrollador de Excel , seleccione Visual Basic y, a continuación, seleccione Insertar > Módulo . Pegue este procedimiento en el módulo:

Sub CopyFormulasToAnotherSheet()
    Dim wb As Workbook
    Dim sourceRange As Range
    Dim targetCell As Range
    Dim targetRange As Range

    Set wb = ThisWorkbook
    Set sourceRange = wb.Worksheets("Source").Range("C2:C10")
    Set targetCell = wb.Worksheets("Summary").Range("C2")
    Set targetRange = targetCell.Resize(sourceRange.Rows.Count, _
                                        sourceRange.Columns.Count)

    targetRange.FormulaR1C1 = sourceRange.FormulaR1C1
End Sub

Modifique Source, Summary, y las direcciones de rango para que coincidan con su libro de trabajo. targetCelles la primera celda de destino; Resizehace que el destino tenga la misma altura y anchura que el origen, lo cual es necesario para una asignación de varias celdas. Para una sola fórmula, establezca el origen en una celda, como C2.

Ejecuta y guarda la macro.

1. Coloque el punto de inserción en cualquier lugar CopyFormulasToAnotherSheetdel Editor de Visual Basic y pulse F5 . También puede volver a Excel, seleccionar Desarrollador > Macros , elegir el procedimiento y seleccionar Ejecutar .
2. Compruebe las celdas de destino en Summary. Con una fórmula de origen como =A2*B2en Source!C2, el patrón de fórmula copiado en Summary!C2también es =A2*B2. Si la fórmula debe seguir leyendo valores en la hoja de origen, califique esas referencias en la fórmula de origen, por ejemplo =Source!A2*Source!B2.
3. Guarde el libro como un libro de Excel habilitado para macros ( .xlsm) para que se conserve el procedimiento VBA. Si Excel le pregunta sobre la seguridad de las macros, ejecute solo las macros en las que confíe.

Elige el método adecuado para tus fórmulas.

FormulaR1C1Copia la estructura de la fórmula. Una referencia situada dos columnas a la izquierda de la celda con la fórmula permanece dos columnas a la izquierda en el rango de destino; las referencias absolutas se mantienen fijas. Esto resulta útil cuando se desea el mismo patrón de cálculo en un rango de destino correspondiente. No copia el formato. Si el rango de origen incluye constantes o celdas en blanco, estas también se transfieren como valores o celdas vacías, por lo que se recomienda usar un rango compuesto por las fórmulas que se desean copiar.

Para un comportamiento normal de copia en Excel, incluyendo fórmulas y formato, utilice Range.Copycon un destino:

wb.Worksheets("Source").Range("C2:C10").Copy _
    Destination:=wb.Worksheets("Summary").Range("C2")

Copia el contenido y el formato completos de la celda; Excel ajusta las referencias relativas como lo hace en una copia estándar. Úselo cuando el destino deba recibir más que fórmulas. Microsoft documenta tanto la propiedad FormulaR1C1 como el método Range.Copy .

Solucionar problemas comunes

• “Subíndice fuera de rango”: Compruebe que los nombres de las hojas de cálculo estén escritos exactamente como aparecen en las pestañas, incluidos los espacios.
• La fórmula apunta a celdas incorrectas: FormulaR1C1 contiene referencias relativas a la hoja de destino. Añada el nombre de la hoja a la fórmula cuando deba seguir apuntando a la hoja de origen.
• El código utiliza el libro de trabajo incorrecto: ThisWorkbook hace referencia al archivo que contiene la macro. Si el código se encuentra en un libro de trabajo de macros personal, indique wbel nombre del libro de trabajo abierto.
• La macro no aparece en la lista: Confirme que el archivo esté guardado con un nombre específico .xlsmy que las macros estén habilitadas para un archivo de confianza. Las instrucciones de Microsoft sobre macros explican cómo habilitar la pestaña Desarrollador y ejecutar un procedimiento.

Cuando se configuran deliberadamente los nombres de origen y destino, los rangos y el comportamiento de las referencias, la macro puede copiar una sola fórmula o un bloque completo sin seleccionar hojas ni depender del portapapeles.