Using VBA in Excel to Transfer Formulas to a Different WorksheetComprehensive Guide with Screenshots

Written By:
Adiste Mae

Excel VBA is an extremely effective tool that enables you to transfer formulas from one sheet to another through programming.

This article will demonstrate the steps for utilizing Excel VBA to replicate formulas on a different sheet.

Initially, we’ll develop a straightforward macro that transfers a formula from one cell to another cell within the same sheet.

Subsequently, we’ll tweak the macro to make it possible to copy the formula to another sheet.

Initiating the process

Recording a macro in Excel will generate VBA code automatically, allowing you to repeat the recorded actions.

However, it’s also possible to create equivalent results through manual VBA coding. This article will guide you through both methods.

How to Record a Macro

  1. To replicate a formula, open the worksheet that contains it.
  2. Click the “Record Macro” button under the “Developer” tab.
  3. Give a name to your macro and, if desired, a shortcut key.
  4. Click “OK.”
  5. Choose the cell with the formula you have to duplicate.
  6. To copy the formula, press “Ctrl” and “C.”
  7. Decide the cell you wish to paste the formula into.
  8. Ctrl + V is used to paste it.
  9. By selecting the “Stop Recording” button under the “Developer” menu, you may stop the macro from recording.

You are now ready to use the macro!

To start a macro, either hit the shortcut key you previously set, or navigate to the “Developer” tab, click the “Macros” button, and then choose your macro from the list.

Crafting VBA Code Manually

Achieving the same results can also be accomplished by composing a limited amount of the VBA code. To proceed, follow the following steps:

  1. Access the workbook that holds the sheet with the formulas you aim to copy.
  2. Use the combination key “Alt” + “F11” to open the Visual Basic Editor.
  3. Press “Insert” and then “Module.”
  4. Enter the code the code below:
    • Sub CopyFormula()

Dim sourceRange As Range

Dim targetRange As Range

Set sourceRange = Range(“A1”) ‘Change A1 to your formula cell

Set targetRange = Range(“B1”) ‘Change B1 to your desired paste cell

targetRange.Formula = sourceRange.Formula

End Sub
  5. Press “F5” to execute .
  6. Now, your formula should be in the cell you desired.

Adjusting the Macro to Transfer Formulas to a Different Sheet

Only the formula located on the same page will be copied by the macro that we created in the previous step.

To duplicate the formula to another sheet, you may quickly edit it.

The steps are as follows:

  1. Access the workbook that holds the sheet with the formulas to be copied.
  2. Use the combination key “Alt” + “F11” to launch the Visual Basic Editor.
  3. To view the code for your macro on the right side of the screen, double-click your macro located on the list on the left side of the screen.
  4. Locate the following code line:
    • Set targetRange = Range(“B1”) ‘Change B1 to your desired paste cell
  5. To change the location where the copied formula is pasted, you need to specify the cell address on a different sheet using the format “SheetName!CellAddress”. For instance, if you desire to paste the formula into cell B2 on a sheet named “Sheet2”, the code should be altered to read “Sheet2!B2”:
    • Set targetRange = Sheets(“Sheet2”).Range(“B2”)
  6. Press “F5” to execute the code. Your formula should now be transferred to the other sheet!

Conclusion:

By the conclusion of this tutorial, you will be able to utilize Excel VBA to move a formula from one sheet to another.

You may use the macro that has been recorded as is or modify it to meet your unique needs. 

I hope this article helps you a lot!