Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Formula Issue
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Formula Issue
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Formula Issue
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Formula Issue
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Formula Issue
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy & Paste Issue | Excel Discussion (Misc queries) | |||
Copy Paste issue | Excel Worksheet Functions | |||
Copy Chart issue | Charts and Charting in Excel | |||
Worksheet copy issue | Excel Programming | |||
Issue with copy & paste? | Excel Discussion (Misc queries) |