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.
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?
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.
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.
Haven't check in Excel, but this works in Libreoffice4:
The whole thing of address rewriting comes during consecutive
You need to interrupt the consecutiveness by putting something in-between:
(b2) select some empty cells (more than 1) and drag(move) them
Step (b2) is where the cell that is about to update itself stops the tracking. Quick and simple.
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.
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.
Simple workaround I used just now while in a similar situation:
Copy of Sheet1) into the original worksheet.
'Copy of Sheet1'!with an empty string (i.e. blank).
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.
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.
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.
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
Click on the cell you want to copy. In the formula bar, highlight the formula.
Press escape (to take you out of actively editing that formula).
Choose new cell. Ctrl V.
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" & vbNewLine & "No unlock ", vbYesNoCancel + vbQuestion, "Forumula locker") If msgResult = vbNo Then For Each cell In Range("A1:i155") If InStr(1, cell.Value, "*") > 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
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 > 0 Then For Each c In ActiveSheet.UsedRange.Cells On Error Resume Next If c.HasFormula Then c.Value = "$" & 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
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" & r.Formula End If Next r
'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