Copy Formula Issue

[Liz]

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

 

[Bernie Deitrick]

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

 

[Alan]

I don't believe you can change the default way Excel copies formulas to
another cell. You can however, if your formula allows, make the formulas
absolute references, example: $A$1 vs. A1. Then the formulas will be copied
"as is". Example

Alan

 

[Liz]

Alan,

Thanks for your quick response. Unfortunately it only worked partly. Here is
my formula in the cell above;

=SUM('Inc.-Inj. Numbers'!V165)

after running the copy function (using the absolute reference logic) here is
the formula in the new row;

=SUM('Inc.-Inj. Numbers'!V166)

This worksheet obviously totals numbers but it's numbers from another
worksheet in the same workbook. A fact I failed to mention in my first post.
My Bad!

Only after reading your response (a big DUH moment for me of course! -
should have clued in on that myself but could not see the forest for the
trees), did I realize that perhaps I need to rethink how I do this as I must
make sure that the reference for the worksheet "Inc.-In.Number" is there
first anyway and the column will change as this will be a new column on the
"Inc.-In.Number" sheet but the row will be the same (each column on the
"Inc.-In.Number" sheet is a new month).

Any hints you can give me in this direction would be greatly appreciated!

Regards,

Liz

 

[Alan]

Thanks to Bernie for submitting that code. It will work great for you if you
want to slip that code into your module.

As far as your formula goes:

=SUM('Inc.-Inj. Numbers'!V165)

To make that an absolute reference formula, you need to add the $ like this:

=SUM('Inc.-Inj. Numbers'!$V$165)


Alan

 

[Liz]

Bernie,

Thanks for the replay and the code! Will let you know how it goes.

Regards,

L