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!

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" <(E-Mail Removed)> wrote in message
news:81D9652B-10C6-4444-A775-(E-Mail Removed)...
>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!

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" wrote:

> 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!

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
--
It it's meant to be, it won't pass you by!


"Alan" wrote:

> 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" wrote:
>
> > 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!

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


> 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
> --
> It it's meant to be, it won't pass you by!
>
>
> "Alan" wrote:
>
> > 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" wrote:
> >
> > > 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!

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" <(E-Mail Removed)> wrote in message
> news:81D9652B-10C6-4444-A775-(E-Mail Removed)...
> >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!
>
>
>