Learn how to copy formulas to another sheet using Excel VBA with easy steps and scripts.

You often need to transfer formulas from one worksheet to another or a sheet of another workbook. You might face consistency and accuracy issues if you manually copy and paste the formulas. Not to mention the time it’ll take to move back and forth multiple worksheets or files.

Here, Excel VBA can help you by automating the whole process. You can create a complete VBA script with all the necessary instructions. Upon execution, Excel will complete the whole task. Alternatively, you can create VBA macros with visual input boxes and dialog boxes to help you copy formulas from one sheet to another.

In this elaborate and proven Microsoft Excel tutorial, I’ll show all such different methods along with easy-to-understand scripts.

ðŸ“’ **Read More**: 3 Ways to Pause or Delay VBA Scripts in Microsoft Excel

## Creating and Running a VBA Macro in Excel

If you’ve already got the necessary Excel skills to create a macro using a VBA script and execute the macro, move on to the scenarios outlined below.

Are you new to Excel VBA? Follow along with the tutorial listed below to learn the technique to create a VBA macro:

ðŸ“’ **Read More**: How To Use The VBA Code You Find Online

Once the VBA macro is ready and saved, press `Alt` + `F8` to access the Macro dialog.

Select the macro you want to execute and hit the Run button.

Now, go through the examples below to copy Excel formulas programmatically:

## Copy Formulas As Is to a New Worksheet

Suppose, you’ve got two datasets in two worksheets of the same workbook. The organization of the datasets is favorable for applying the same set of formulas from the first dataset to the second.

An example of the source dataset with a simple formula in **column D** is shown in the above screenshot.

The destination dataset of this exercise is as shown above.

I’ll show you how to copy the following formula from **Sheet1** **column D** to **Sheet2** **column D**.

`=B2*C2 `

Use the VBA script outlined below:

```
Sub CopyFormulaToDestination()
Dim sourceRange As Range
Dim destRange As Range
On Error Resume Next
Set sourceRange = Application.InputBox("Select the source cell or range:", Type:=8)
On Error GoTo 0
If sourceRange Is Nothing Then Exit Sub
On Error Resume Next
Set destRange = Application.InputBox("Select the destination cell or range:", Type:=8)
On Error GoTo 0
If destRange Is Nothing Then Exit Sub
sourceRange.Copy Destination:=destRange
destRange.Worksheet.Calculate
End Sub
```

Once you execute the script, Excel will show a pop-up box so you can choose the source of the formula from one or many cells. You can choose the cells either row-wise or column-wise, as required.

The next input box will ask you to select the destination cell or cell range.

Both of the input boxes will allow you to swap the worksheets as needed.

Once you’ve interacted appropriately with the prompts, Excel will copy the formulas instantly.

The VBA script transfers the selected formulas with relative references. Therefore, the formula will calculate values using the dataset in the destination worksheet.

ðŸ“’ **Read More**: How To Add A Form Control Button To Run Your VBA Code

## Copy Only the Formula to Another Sheet

If you wish to copy the formula syntaxes and not a functional formula, use the following VBA script instead. This method allows you to list used formulas in another worksheet for learning purposes.

Add the following VBA script in between the `destRange.Worksheet.Calculate`

and `End Sub`

code lines of the VBA script of the previous method.

```
' Get formula syntax in adjacent cells
For Each cell In destRange
cell.Offset(0, 1).Formula = "=FORMULATEXT(" & cell.Address & ")"
Next cell
```

Now, save the script and create a macro using it.

Excel will show input boxes so you can choose the source and destination cell ranges.

Ensure that there’s an empty column to the right of the destination cell range.

VBA script will automatically populate the formula syntaxes in the adjacent cells of the destination cell range.

## Copy Formulas With Specific Excel Functions

The following script allows you to selectively copy formulas with specific functions from one worksheet to the other.

```
Sub CopyFormulas()
Dim sourceFormula As String
Dim targetRange As Range
Dim cell As Range
sourceFormula = InputBox("Enter the formula to copy (e.g., AVERAGE, SUM, etc.):")
On Error Resume Next
Set targetRange = Application.InputBox("Select the destination cell or cell range:", Type:=8)
On Error GoTo 0
If targetRange Is Nothing Then Exit Sub
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
If cell.Formula Like "*" & sourceFormula & "*" Then
cell.Copy targetRange
Set targetRange = targetRange.Offset(1)
End If
Next cell
MsgBox "Formulas copied successfully!", vbInformation
End Sub
```

Go to the source worksheet and execute the VBA macro created with the above script.

A prompt will ask you to enter the function to copy. You can simply enter the name of the function, like **SUM**, **AVERAGE**, **IF**, etc., whichever you expect to find in the source worksheet.

Use the destination dialog box to pick a cell or cell range where you want to copy the chosen function and its formula.

Excel will copy the formula instantly.

## Copy Formulas to a New Sheet With Error Handling

When you’re copying formulas from a master worksheet and find that the formulas don’t have any error handling, use the following script. It addressed the formula error handling with the **IFERROR** function of Excel.

```
Sub CopyFormulasWithIFERROR()
Dim sourceRange As Range
Dim destRange As Range
Dim cell As Range
' Prompt user to select the source range
On Error Resume Next
Set sourceRange = Application.InputBox("Select the source range:", "Source Range", Type:=8)
On Error GoTo 0
If sourceRange Is Nothing Then Exit Sub
' Prompt user to select the destination range
On Error Resume Next
Set destRange = Application.InputBox("Select the destination range:", "Destination Range", Type:=8)
On Error GoTo 0
If destRange Is Nothing Then Exit Sub
' Ensure the source and destination ranges have the same size
If sourceRange.Rows.Count <> destRange.Rows.Count Or sourceRange.Columns.Count <> destRange.Columns.Count Then
MsgBox "Source and destination ranges must have the same size.", vbExclamation
Exit Sub
End If
' Loop through each cell in the source range
For Each cell In sourceRange
' Get the relative address of the cell within the source range
Dim relativeAddress As String
relativeAddress = cell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
' Get the destination cell corresponding to the current cell in the source range
Dim destCell As Range
Set destCell = destRange.Cells(cell.Row - sourceRange.Row + 1, cell.Column - sourceRange.Column + 1)
' Check if the cell contains a formula
If cell.HasFormula Then
' Wrap the formula in IFERROR and copy it to the destination cell
destCell.formula = "=IFERROR(" & Mid(cell.formula, 2) & ",""Formula Error"")"
End If
Next cell
MsgBox "Formulas copied successfully with IFERROR wrapped.", vbInformation
End Sub
```

Suppose, you’re copying formulas from a worksheet that contains the above dataset.

The underlying formula in **column D** is `=C2/B2`

and it’s calculating all the cells correctly.

However, if you copy the same formula in the dataset shown above, you’ll get `#DIV/0`

! error because some of the cells in **column B** contain `0`

.

So, you run the updated VBA script given above, which shows you an input box so you can pick the source cells to extract formulas.

Then, you use the second prompt to choose the destination cell range.

Before copying and pasting the formulas, Excel would wrap the copied formulas in the **IFERROR** function. The **IFERROR** text used will be **Formula Error**.

Find above the outcome of this script.

## Copy Formulas to a Sheet in Another Workbook

If the destination worksheet is in a different Excel workbook, follow the methods mentioned below:

### If the Workbook Is Open

You can use the same VBA script as you’ve seen in the first method mentioned earlier.

The above screenshot shows that I’ve selected the source range from the **Copy Formulas to Another Sheet.XLXS** workbook.

Then, the script shows visual input boxes to select the destination cell range. So, I’ve selected the **Test 1.XLXS** workbook on the **Windows Taskbar** and then chose the desired worksheet.

Excel will copy your formulas from source to destination.

### If the Workbook Is Closed

Use this script to get a dialog box to open the destination Excel file when copying and pasting formulas from one sheet to another using Excel VBA:

```
Sub CopyFormulaToAnotherWorkbook()
Dim srcRange As Range
Dim destRange As Range
Dim destWorkbook As Workbook
Dim destFilePath As String
Dim fd As FileDialog
' Prompt to select the source cell or range
On Error Resume Next
Set srcRange = Application.InputBox("Select the source cell or range", Type:=8)
On Error GoTo 0
If srcRange Is Nothing Then Exit Sub
' Open file dialog to select the destination workbook
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Title = "Select the Destination Workbook"
.Filters.Clear
.Filters.Add "Excel Files", "*.xls; *.xlsx; *.xlsm; *.xlsb"
.AllowMultiSelect = False
If .Show = -1 Then
destFilePath = .SelectedItems(1)
Else
Exit Sub
End If
End With
' Open the destination workbook
Set destWorkbook = Workbooks.Open(destFilePath)
' Prompt to select the destination cell or range
On Error Resume Next
Set destRange = Application.InputBox("Select the destination cell or range", Type:=8)
On Error GoTo 0
If destRange Is Nothing Then Exit Sub
' Copy the formula
destRange.formula = srcRange.formula
' Inform the user of successful operation
MsgBox "Formula copied successfully!", vbInformation
' Cleanup
Set srcRange = Nothing
Set destRange = Nothing
Set destWorkbook = Nothing
Set fd = Nothing
End Sub
```

The above script will show a prompt for the input cell range.

Then, you’ll see a dialog box to browse files in your local PC or network so you can open the closed workbook.

You can now navigate to the newly opened workbook, select a destination range, and click OK.

Excel will export the copied formulas to a sheet of the new **XLXS** file.

## Copy Formulas to Multiple Worksheets

If you wish to copy the formulas of one worksheet to multiple sheets in the same or different workbook, use the following script:

```
Sub CopyFormulasMultipleTimes()
Dim srcRange As Range
Dim destRange As Range
Dim i As Integer
Dim numCopies As Integer
Dim prompt As String
On Error Resume Next
Set srcRange = Application.InputBox("Select the source cell range:", "Source Range", Type:=8)
On Error GoTo 0
If srcRange Is Nothing Then
MsgBox "Source range selection cancelled.", vbExclamation
Exit Sub
End If
numCopies = Application.InputBox("Enter the number of times to copy and paste the formulas:", "Number of Copies", Type:=1)
If numCopies <= 0 Then
MsgBox "Invalid number of copies entered.", vbExclamation
Exit Sub
End If
For i = 1 To numCopies
prompt = "Select the destination range " & i & " (top-left cell):"
On Error Resume Next
Set destRange = Application.InputBox(prompt, "Destination Range " & i, Type:=8)
On Error GoTo 0
If destRange Is Nothing Then
MsgBox "Destination range selection cancelled.", vbExclamation
Exit Sub
End If
srcRange.Copy
destRange.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
Next i
MsgBox "Formulas copied successfully.", vbInformation
End Sub
```

After running the macro, interact with the following input boxes appropriately:

- Select the source cell range to copy the formula.

- Enter a numeric value for the number of copies you want.

- Choose a range for the first destination.

- Select the cell range for the second destination.

Excel will keep copying the formulas in the background as you keep on selecting new destination worksheets.

## Conclusions

So far, you’ve learned seven different ways to copy formulas to another sheet using Excel VBA.

You’ll need to choose the right VBA script from the examples above that match the dataset and scenario you’re working on.

If the article helped you or you’d like to share some suggestions, use the comment box.

## 0 Comments