Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

When you press CTRL+D in Excel 2007 or 2010 (and probably older versions), it makes an exact copy of the cell above it (modifying cell references, of course). In doing so, it copies everything else like font formatting, fills, and even borders (ugh). If I could just copy the value/forumula and just inherit the current cell's formatting, that'd be awesome.

Update:

An answerer has recommended I use a macro. Using their idea presented in their answer, I came up with this macro:

Update 2:

Unfortunately, I cannot use this. If I have anything stored in my clipboard, this macro will cause that data to be lost. I will still keep this here for anyone who's not bothered by that.

Sub ctrld()

Dim scrup As Boolean

scrup = Application.ScreenUpdating

Application.ScreenUpdating = False

Selection.Offset(-1, 0).Copy
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False

Application.ScreenUpdating = scrup

End Sub

Unless anyone knows of a more native approach to this then that answerer will get the mark.

share|improve this question
add comment

2 Answers

You can copy only formulas and values, leaving the formatting behind by using the keyboard shortcut Ctrl - ' -- that's the Ctrl key and the single quote.

Or you can use the fill handle to drag down and then select the Autofill options drop-down with "Fill Without Formatting".

enter image description here

share|improve this answer
    
Unfortunately, CTRL+' doesn't appear to modify cell references (a requirement in my question). Instead of modifying =A1+A2 to be A2+A3, it copies formula verbatim. Also, dragging the fill handle down with the mouse defeats the purpose of a keyboard shortcut. I'd like to be able to press CTRL+[whatever] and have it do everythign that CTRL+D does without it copying the formatting. –  oscilatingcretin May 13 '13 at 13:23
add comment

In the ribbon: go to View tab-->unhide-->personal.xlsb-->Ok-->record new macro--> In the dialog box: name:"pasteVvalue"-->shortcut "crtl+D"-->record in "personal macro folder"-->ok Use any random command from the ribbon-->stop recording-->display macro:"pasteValue"-->erase all vba code between the line Sub pasteValue() and End sub insert the following--> save

 Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

From now on crtl+d will behave the way you want (copy value or formula in changing references) in all Excel files you're using on your computer.

share|improve this answer
    
You forgot to actually copy the text in your macro. You also used xlPasteValues instead of xlPasteFormulas. I've updated my question with a solution using your idea. If no one else advises of a more native method of accomplishing this, you'll get the answer. –  oscilatingcretin May 13 '13 at 18:07
    
Actually, wait, I can't use this. I just realized that copying a cell will blow out whatever I have stored in my clipboard, so this won't work. –  oscilatingcretin May 13 '13 at 18:10
    
I've edited my answer, thanks. –  Jo Bedard May 13 '13 at 19:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.