Copy Formula Issue
Bernie,
Thanks for the replay and the code! Will let you know how it goes.
Regards,
L
--
It it's meant to be, it won't pass you by!
"Bernie Deitrick" wrote:
L,
Try the code below - written to copy the row of the activecell.
HTH,
Bernie
MS Excel MVP
Sub CopyRowWithExactFormulas()
Dim myCalc As Variant
With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With
With ActiveCell.EntireRow
SAFormulaToText .Cells
.Copy
.Insert xlDown
SATextToFormula .Cells
SATextToFormula .Offset(-1, 0).Cells
End With
With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub
Sub SAFormulaToText(myR As Range)
Dim myCell As Range
On Error Resume Next
For Each myCell In myR.SpecialCells(xlCellTypeFormulas)
myCell.Formula = "'" & myCell.Formula
Next myCell
End Sub
Sub SATextToFormula(myR As Range)
Dim myCell As Range
On Error Resume Next
For Each myCell In myR
myCell.Formula = myCell.Text
Next myCell
End Sub
"Liz" wrote in message
...
I have a macro that copies a row and pastes it immediately below. The cells
in the row being copied each a a different formula in them and I want these
individual formulas to be copied to the matching cells in the new row. My
code does this however the cell references in the new row is not the same as
the cell references in the row above it and should be. Is this an automatic
thing that Excel does and how can I turn it off or code it so that the
formula is copied exactly as is!
Any assistance would be greatly appreciated.
L
--
It it''''s meant to be, it won''''t pass you by!
|