How to disable Excel s automatic cell reference change after copy paste

Question

I ve got a massive Excel 2003 spreadsheet I m working on  There are a lot of very large formulas with a lot of cell references  Here s a simple example     Sheet  AC69  Sheet  AC52  Sheet  AC53   D 3  Sheet  AC49   Most of them are more complicated than that  but this gives a good idea of what I m working with  Few of these cell references are absolute   s   I d like to be able to copy these cells to a different location without the cell references changing  I know I can simply use f4 to make the references absolute  but there is a lot of data and I may need to use Fill later  Is there any way to temporarily disable the cell reference changing on copy-paste fill without making the references absolute   EDIT  I just found out that you can do this with VBA by copying the cell contents as text instead of a formula  I d like to not have to do this though because I want to copy whole rows columns at once  Is there a simple solution I am missing

User · Answer

I came to this site looking for an easy way to copy without changing cell references. But now I thnk my own workaround is simpler than most of these methods. My method relies on the fact that Copy changes references but Move doesn't. Here's a simple example.

Assume you have raw data in columns A and B, and a formula in C (e.g. C=A+B) and you want the same formula in column F but Copying from C to F leads to F=D+E.

Copy contents of C to any empty temporary column, say R. The relative references will change to R=P+Q but ignore this even if it's flagged as an error.
Go back to C and Move (not Copy) it to F. Formula should be unchanged so F=A+B.
Now go to R and copy it back to C. Relative reference will revert to C=A+B
Delete the temporary formula in R.
Bob's your uncle.

I've done this with a range of cells so I imagine it would work with virtually any level of complexity. You just need an empty area to park the coiped cells. And of course you have to remember where you left them.

User · Answer

This macro does the whole job   Sub Absolute Reference Copy Paste    By changing     in formulas to     the content is no longer seen as a formula    C S e  my keyboard shortcut   Dim Dummy As Range Dim FirstSelection As Range Dim SecondSelection As Range Dim SheetFirst As Worksheet Dim SheetSecond As Worksheet  On Error GoTo Whoa  Application EnableEvents   False    Set starting selection variable  Set FirstSelection   Selection Set SheetFirst   FirstSelection Worksheet    Reset the Find function so the scope of the search area is the current worksheet  Set Dummy   Worksheets 1  Range  A1 A1   Find  Dummy   LookIn  xlValues     Change     to     in selection  Selection Replace What       Replacement       LookAt  xlPart        SearchOrder  xlByRows  MatchCase  False  SearchFormat  False  ReplaceFormat  False    Select the area you want to paste the formulas  must be same size as original   selection and outside of the original selection  Set SecondSelection   Application InputBox  Select a range    Obtain Range Object   Type  8  Set SheetSecond   SecondSelection Worksheet    Copy the original selection and paste it into the newly selected area  The active   selection remains FirstSelection  FirstSelection Copy SecondSelection    Restore     in FirstSelection  Selection Replace What       Replacement       LookAt  xlPart    SearchOrder  xlByRows  MatchCase  False  SearchFormat  False  ReplaceFormat  False    Select SecondSelection  SheetSecond Activate SecondSelection Select    Restore     in SecondSelection  Selection Replace What       Replacement       LookAt  xlPart    SearchOrder  xlByRows  MatchCase  False  SearchFormat  False  ReplaceFormat  False    Return active selection to the original area  FirstSelection  SheetFirst Activate FirstSelection Select  Application EnableEvents   True  Exit Sub  Whoa    If something goes wrong after     has been changed in FirstSelection  restore      FirstSelection Select Selection Replace What       Replacement       LookAt  xlPart        SearchOrder  xlByRows  MatchCase  False  SearchFormat  False  ReplaceFormat  False  End Sub   Note that you must match the size and shape of the original selection when you make your new selection

User · Answer

I found another workaround that is very simple  1  Cut the contents 2  Paste them in the new location 3  Copy the contents that you just pasted into the new location you want  4  Undo the Cut-Paste operation  putting the original contents back where you got them  5  Paste the contents from the clipboard to the same location   These contents will have the original references   It looks like a lot  but is super fast with keyboard shortcuts  1  Ctrl-x  2  Ctrl-v  3  Ctrl-c  4  Ctrl-z  5  Ctrl-v

User · Answer

I think that you re stuck with the workaround you mentioned in your edit   I would start by converting every formula on the sheet to text roughly like this   Dim r As Range  For Each r In Worksheets  Sheet1   UsedRange     If  Left  r Formula  1         Then         r Formula     ZZZ   amp  r Formula     End If Next r   where the  ZZZ uses the   to signify a text value and the ZZZ as a value that we can look for when we want to convert the text back to being a formula  Obviously if any of your cells actually start with the text ZZZ then change the ZZZ value in the VBA macro to something else  When the re-arranging is complete  I would then convert the text back to a formula like this   For Each r In Worksheets  Sheet1   UsedRange     If  Left  r Formula  3     ZZZ   Then         r Formula   Mid  r Formula  4      End If Next r   One real downside to this method is that you can t see the results of any formula while you are re-arranging  You may find that when you convert back from text to formula that you have a slew of  REF errors for example   It might be beneficial to work on this in stages and convert back to formulas every so often to check that no catastrophes have occurred

User · Answer

It s common to use find  replace to disable formulas whilst performing manipulations  For example  copy with transpose  The formula is disabled by placing some placeholder  e g        in front of it  Find replace can get rid of these once the manipulation is complete    This vba just automates the find  replace for the active sheet     toggle forumlas on active sheet as active  inactive   by use of      prefix  Sub toggle active formulas       Dim current calc method As String     initial calc method   Application Calculation     Application Calculation   xlCalculationManual     Dim predominant As Integer     Dim c As Range     For Each c In ActiveSheet UsedRange Cells         If c HasFormula Then             predominant   predominant   1         ElseIf        Left c Value  2  Then             predominant   predominant - 1         End If     Next c     If predominant  gt  0 Then         For Each c In ActiveSheet UsedRange Cells             On Error Resume Next             If c HasFormula Then                 c Value        amp  c Formula             End If         Next c     Else         For Each c In ActiveSheet UsedRange Cells             On Error Resume Next             If        Left c Value  2  Then                 c Formula   Right c Value  Len c Value  - 1              End If         Next c     End If     Application Calculation   initial calc method End Sub

User · Answer

A very simple solution is to select the range you wish to copy  then Find and Replace  Ctrl   h   changing   to another symbol that is not used in your formula  e g     - thus stopping it from being an active formula   Then  copy and paste the selected range to it s new location     Finally  Find and Replace to change   back to   in both the original and new range  thus restoring both ranges to being formulae again

User · Answer

I found this solution which automates  Alistair Collins solution    Basically you will change the   in any formula to   then do the paste after that you will change it back            Dim cell As Range  msgResult   MsgBox  Yes to lock   amp  vbNewLine  amp   No unlock    vbYesNoCancel   vbQuestion   Forumula locker    If msgResult   vbNo Then     For Each cell In Range  A1 i155           If InStr 1  cell Value        gt  0 Then             cell Formula   Replace cell Formula                    End If     Next cell ElseIf msgResult   vbYes Then     For Each cell In Range  A1 i155           If cell HasFormula   True Then             cell Formula   Replace cell Formula                    End If     Next cell End If

User · Answer

Simple workaround I used just now while in a similar situation   Make a duplicate of the worksheet  Cut   Paste the cells with formulas from the duplicate worksheet  e g  Copy of Sheet1  into the original worksheet  Remove all occurrences of the the duplicate sheet s name from the newly pasted formulas  e g  find-and-replace all occurrences of the search term  Copy of Sheet1   with an empty string  i e  blank   Delete duplicate worksheet to clean up   Note  if your sheet name lacks spaces you won t need to use the single quote apostrophe      Your cell references are now copied without being altered

User · Answer

From http   spreadsheetpage com index php tip making an exact copy of a range of formulas take 2    Put Excel in formula view mode  The easiest way to do this is to press Ctrl    that character is a  backwards apostrophe   and is usually on the same key that has the    tilde   Select the range to copy  Press Ctrl C Start Windows Notepad Press Ctrl V to past the copied data into Notepad In Notepad  press Ctrl A followed by Ctrl C to copy the text Activate Excel and activate the upper left cell where you want to paste the formulas  And  make sure that the sheet you are copying to is in formula view mode  Press Ctrl V to paste  Press Ctrl   to toggle out of formula view mode    Note  If the paste operation back to Excel doesn t work correctly  chances are that you ve used Excel s Text-to-Columns feature recently  and Excel is trying to be helpful by remembering how you last parsed your data  You need to fire up the Convert Text to Columns Wizard  Choose the Delimited option and click Next  Clear all of the Delimiter option checkmarks except Tab   Or  from http   spreadsheetpage com index php tip making an exact copy of a range of formulas    If you re a VBA programmer  you can simply execute the following code   With Sheets  Sheet1     Range  A11 D20   Formula    Range  A1 D10   Formula End With

User · Answer

Click on the cell you want to copy   In the formula bar  highlight the formula   Press Ctrl C     Press escape  to take you out of actively editing that formula      Choose new cell   Ctrl V

User · Answer

This simple trick works     Copy and Paste   Do NOT cut and paste   After you paste  then reselect the part you copied and go to EDIT  slide down to CLEAR  and CLEAR CONTENTS

User · Answer

Try this  Left-click the tab  making a copy of the whole sheet  then cut the cells you want to keep the same and paste them into your original worksheet

User · Answer

Haven t check in Excel  but this works in Libreoffice4   The whole thing of address rewriting comes during consecutive  a1  cut  a2  paste  You need to interrupt the consecutiveness by putting something in-between    b1  cut   b2  select some empty cells  more than 1  and drag move  them  b3  paste  Step  b2  is where the cell that is about to update itself stops the tracking  Quick and simple

[excel] How to disable Excel's automatic cell reference change after copy/paste?

Examples related to excel

Examples related to excel-formula

Examples related to reference

Examples related to cell

Examples related to copy-paste