Copy formulas to another workbook without links

How to avoid links or references in formulas copied from one workbook to another?

The simple way is to search and replace the reference with nothing, but if it is something you do frequently, it is smarter to use a VBA macro (see example below). First a short explanation.

Say you have a workbook called "formulas" and you want to copy a cell with the formula "=jan2016!E4" to another workbook, the pasted formula will change to:

i.e. with a link or reference to the original workbook. So what do you do, if you want the original formula, "=jan2016!E4", without the reference to "formulas.xls"?

To try the macro highlight it with the mouse, copy (CTRL+C) and paste it (CTRL+V) into a VBA module. Let's go!

Sub CopyFormulas()
Dim bOpen As Boolean
Dim arArray()
Dim rTable As Range
Dim rTarget As Range
Dim sFileName
Dim Wb As Workbook

On Error Resume Next

'You can define a range in many ways.
'Here we ask the user to select the
'range that he wants to copy.
Set rTable = Application.InputBox _
("Select the range to copy.", _
"Select range", , , , , , 8)

If Not rTable Is Nothing Then
   On Error GoTo ErrorHandle
   
   If rTable.Count = 1 Then
      MsgBox "There must be more than 1 cell"
      GoTo BeforeExit
   End If
   
   'Copies the formulas to an array. The
   'array will automatically get the same
   'dimensions as the range.
   'The trick to get the formulas is to write
   '"rTable.Formula".
   arArray = rTable.Formula
   
   'Show a file open dialogue. Select
   'the target workbook. This doesn't open
   'the workbook, it just returns the file
   'name and the path.
   sFileName = Application.GetOpenFilename _
   ("Excel files (*.xls*),*.xls*", , _
   "Select the target workbook")
   
   If sFileName = False Then GoTo BeforeExit
   
   'If there is more than one open workbook,
   'we check if it is already open. If it is,
   'we just activate it.
   For Each Wb In Workbooks
      If Wb.FullName = sFileName Then
         Wb.Activate
         bOpen = True
         Exit For
      End If
   Next
   
   'If the target workbook isn't open, we open it
   If bOpen = False Then
      Workbooks.Open (sFileName)
   End If
   
   On Error Resume Next
   
   'Ask the user to select insertion point
   Set rTarget = Application.InputBox _
   ("Select cell for the table's upper left corner", _
   "Select target", , , , , , 8)

   'If the user selected a cell
   If Not rTarget Is Nothing Then
      On Error GoTo ErrorHandle
      
      'Dimension the range to the same dimensions
      '(rows, columns) as the array.
      Set rTable = rTarget.Resize(UBound(arArray), UBound(arArray, 2))
      'Paste the formulas
      rTable.Formula = arArray
   End If
End If

BeforeExit:
On Error Resume Next
Set rTable = Nothing
Set rTarget = Nothing
Erase arArray

Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

That was it. Of course it is important, that if the formula contains a reference to a named sheet or range (e.g. "jan2016"), the target workbook must have a sheet or range with the same name - otherwise the formula will point to something that doesn't exist.

I have used this technique for various purposes, and you can read more about it on the page Arrays and range formulas.

In general it is good to know how to copy a range into an array and vice versa in one swift operation (instead of looping), because it is very fast.