Cell range copy to second workbook

[GerryK]

Hi,
I'm trying to copy a range of cells from one workbook to
another WYSIWYG but keep getting extended path names
within my formulas each time there is a new condition
referencing another sheet within that workbook. This makes
my formula unwieldy in terms of manually cleaning them up.
The new workbook then requires updating from the original
one upon opening.
Is there a way to copy and paste into another workbook so
the formulas are the way I wrote them when placed into the
target location?

TIA

 

[Bernie Deitrick]

Gerry,

Select your formula cells, run a macro to convert the formulas to text, then
copy and paste the text, and reconvert to formulas.
Below are two macros that you could use to do the conversion.

HTH,
Bernie
MS Excel MVP

Sub FormulaToText()
Dim myCell As Range
For Each myCell In Selection
myCell.Formula = "'" & myCell.Formula
Next myCell
End Sub

Sub TransformToFormula()
Dim myCell As Range
On Error Resume Next
For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell
End Sub

 

[Gord Dibben]

Gerry

Couple of methods.

1. Select the cells and Edit>Replace the = signs with xxx

Copy the cells to the new workbook then reverse the Replace procedure.

2. Use a macro.

Sub CopyFormulasExact()
Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim intColCount As Integer
Dim intRowCount As Integer

' Check that a range is selected
If Not TypeName(Selection) = "Range" Then End
' check that the range has only one area
If Not Selection.Areas.Count = 1 Then
MsgBox "Multiple Selections Not Allowed", vbExclamation
End
End If

' Assign selection to object variable
Set rngCopyFrom = Selection
If Not Selection.HasFormula Then
MsgBox "Cells do not contain formulas"
End
End If

' This is required in case cancel is clicked.
' Type 8 input box returns a range object if OK is
' clicked or False if cancel is clicked. I do not
' know of a way to test for both cases without
' using error trapping
On Error GoTo UserCancelled

' Assign object variable to user-selected cell
Set rngCopyTo = Application.InputBox( _
prompt:="Select the UPPER LEFT CELL of the " _
& "range to which you wish to paste", _
Title:="Copy Range Formulae", Type:=8).Cells(1, 1)

On Error GoTo 0

' Loop through source range assigning any formulae found
' to the equivalent cell of the destination range.
For intColCount = 1 To rngCopyFrom.Columns.Count
For intRowCount = 1 To rngCopyFrom.Rows.Count
If rngCopyFrom.Cells(intRowCount, _
intColCount).HasFormula Then
rngCopyTo.Offset(intRowCount - 1, _
intColCount - 1).Formula = _
rngCopyFrom.Cells(intRowCount, _
intColCount).Formula
End If
Next intRowCount
Next intColCount

UserCancelled:
End Sub

Gord Dibben Excel MVP