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.