Clash Royale CLAN TAG#URR8PPP

Is there anyway to make copy in Excel behave like cut?

I'm having a lot of problems here... If I copy it gets messed up since I am not pasting in the same exact spot. If I cut I lose my block of information. If i do = then click and drag it gets messed up since the cells I am copying are actually references, so when I sort those references its bad news bears.
IE Sheet1 A1:E5 = data1 information
Sheet2 Q5:U9 = 'Sheet1'!A1:A5

On sheet 3, because of the way sheet 2 is setup, I cannot just go directly from sheet1 (due to sorting). I know this is a little confusing but TL;DR, is there anyway to emulate "Cut" without deleting the cells? .

3 Answers
3

If the cell references need to be constant regardless of where the equations are moved to, use something like $A$1:$A$5 for your range to fix the rows and columns in place. (the $ is an indication to Excel to not adjust the value of the index that follows it [either row or column], so if you have an equation in cell A1 that references the range $C3:$C19, copying the equation to cell B2 would result in the equation referencing range $C4:$C20, and if the range was C$3:C$19 then copying the equation to cell B2 would result in the equation referencing range D$3:D$19, while using $C$3:$C$19 would result in the referenced range being unchanged).

$A$1:$A$5

$

$C3:$C19

$C4:$C20

C$3:C$19

D$3:D$19

$C$3:$C$19

You could also look into using R1C1 notation, but I don't think that's necessary for you.

Other than manually entering $ is there a way excel can do it automatically?
– Dan Van
Jun 14 '13 at 13:27

Have you tried doing a Paste Special?

If you have Excel 2010 (may even be true of 2007), then you can see the effect of paste before actually pasting.

Now, if you need the references to the cells in Sheet1 to remain on the copy, then you will need to add the $ in front of the cells as JAB has pointed out, then you can do a Paste Special -> Formulas

Possible simple VBA Code:

Sub ConvertUserRangeToAbsolute()
Dim cell As Object
For Each cell In Selection
cell.Formula = Application.ConvertFormula(Formula:=cell.Formula, fromreferencestyle:=Application.ReferenceStyle, toabsolute:=xlAbsolute)
Next cell
End Sub


Modified Source: http://www.mrexcel.com/forum/excel-questions/29368-visual-basic-applications-change-formula-relative-absolute-reference.html#post134975

Hope this helps.

Only problem is my cells dont have the $ i have something that converts the sheet into $, is there a way to make this do it on a selection instead of the entire sheet? 'Sub ConvertToAbsolute() ' ' ' ' Keyboard Shortcut: Ctrl+j ' For Each cell In ActiveSheet.UsedRange If cell.HasFormula = True Then cell.Formula = Application.ConvertFormula _ (Formula:=cell.Formula, _ FromReferenceStyle:=xlA1, _ ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute) End If Next End Sub'
– Dan Van
Jun 14 '13 at 13:50

There's a Find/Replace trick that is discussed here: link. 1. Highlight the range of cells you want to copy. 2. Do CTRL-H (or click on the Home ribbon and then click on the Find & Select (binocular) button and select Replace. 3. In the Find field, type =, and in the Replace field type A=. Then hit Replace All. 4. Copy the selection and paste it in desired location. 5. Open the Find & Replace window again (see step 2). 6. In the Find field, type A= and in the Replace field type =. Then hit Replace All.
– jtgaut17
Jun 14 '13 at 21:23

I've added a simple VBA script which might be what you are looking for.
– jtgaut17
Jun 14 '13 at 21:41

I just copy the original to an adjoining cell, then go back to the original and cut it and put it where I want it, then copy the copy back to the original place of the original. It seems screw it up, then unscrew it up.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.