Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy and Pasting Formulas
Hi,
I need to copy a formula, but when I paste it, it needs to stay the same formula. Example: I am copying a cell with this formula in A1 that is: =B1+C1 to cell A2. However, when I paste it in A2, the formula changes to: = B2+C2 and I want it to stay as =B1+C1. I understand that I could use $, but this workbook has already been created by someone else and I am trying to copy and paste a large block of cells, where I don't want to go through and edit every cell. Is there a way to do this? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy and Pasting Formulas
Use one of these macros to change all cells at one whack.
Sub Absolute() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlAbsolute) End If Next End Sub Sub AbsoluteRow() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlAbsRowRelColumn) End If Next End Sub Sub AbsoluteCol() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn) End If Next End Sub Sub Relative() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlRelative) End If Next End Sub Gord Dibben MS Excel MVP On Thu, 10 Sep 2009 15:17:01 -0700, Lindsey wrote: Hi, I need to copy a formula, but when I paste it, it needs to stay the same formula. Example: I am copying a cell with this formula in A1 that is: =B1+C1 to cell A2. However, when I paste it in A2, the formula changes to: = B2+C2 and I want it to stay as =B1+C1. I understand that I could use $, but this workbook has already been created by someone else and I am trying to copy and paste a large block of cells, where I don't want to go through and edit every cell. Is there a way to do this? Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy and Pasting Formulas
Sorry, I do not know how to create macros. If they are easy to create or
there is a website that can introduce me to them, please let me know, thanks! "Gord Dibben" wrote: Use one of these macros to change all cells at one whack. Sub Absolute() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlAbsolute) End If Next End Sub Sub AbsoluteRow() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlAbsRowRelColumn) End If Next End Sub Sub AbsoluteCol() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn) End If Next End Sub Sub Relative() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlRelative) End If Next End Sub Gord Dibben MS Excel MVP On Thu, 10 Sep 2009 15:17:01 -0700, Lindsey wrote: Hi, I need to copy a formula, but when I paste it, it needs to stay the same formula. Example: I am copying a cell with this formula in A1 that is: =B1+C1 to cell A2. However, when I paste it in A2, the formula changes to: = B2+C2 and I want it to stay as =B1+C1. I understand that I could use $, but this workbook has already been created by someone else and I am trying to copy and paste a large block of cells, where I don't want to go through and edit every cell. Is there a way to do this? Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy and Pasting Formulas
I need to copy a formula, but when I paste it, it needs to stay the
same formula. Example: I am copying a cell with this formula in A1 that is: =B1+C1 to cell A2. However, when I paste it in A2, the formula changes to: = B2+C2 and I want it to stay as =B1+C1. One way is to select A1, then select the text in the formula bar to copy (instead of copying the cell A1 itself). Then select A2, click in the formula bar, and paste into the formula bar. (I have Excel 2003.) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy and Pasting Formulas
If you're still looking.................
If you're not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm or Ron de De Bruin's site on where to store macros. http://www.rondebruin.nl/code.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + r to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run or edit the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. I would sugeest you use the Sub Absolute() macro. Gord On Fri, 11 Sep 2009 06:39:01 -0700, Lindsey wrote: Sorry, I do not know how to create macros. If they are easy to create or there is a website that can introduce me to them, please let me know, thanks! "Gord Dibben" wrote: Use one of these macros to change all cells at one whack. Sub Absolute() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlAbsolute) End If Next End Sub Sub AbsoluteRow() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlAbsRowRelColumn) End If Next End Sub Sub AbsoluteCol() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn) End If Next End Sub Sub Relative() Dim Cell As Range For Each Cell In Selection If Cell.HasFormula Then Cell.Formula = Application.ConvertFormula _ (Cell.Formula, xlA1, xlA1, xlRelative) End If Next End Sub Gord Dibben MS Excel MVP On Thu, 10 Sep 2009 15:17:01 -0700, Lindsey wrote: Hi, I need to copy a formula, but when I paste it, it needs to stay the same formula. Example: I am copying a cell with this formula in A1 that is: =B1+C1 to cell A2. However, when I paste it in A2, the formula changes to: = B2+C2 and I want it to stay as =B1+C1. I understand that I could use $, but this workbook has already been created by someone else and I am trying to copy and paste a large block of cells, where I don't want to go through and edit every cell. Is there a way to do this? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy & pasting automatic formulas | Excel Worksheet Functions | |||
Copy & pasting formulas across workbook tabs | Excel Worksheet Functions | |||
Copy/Pasting formulas | New Users to Excel | |||
Pasting formulas | Excel Discussion (Misc queries) | |||
Pasting numbers and formulas without pasting format. | Excel Discussion (Misc queries) |