Excel 2007 Cut and paste formula or value without changing cell formatting - paste options greyed out

Question

I just need to cut cell contents leaving the source cell format unchanged, and paste matching destination cell formatting... I think not an uncommon ask?

I have searched but Google search filter bubble has got me - nada.

Paste special is not available for cuts? Paste options are greyed out.

I have tried recording a macro to copy with the clipboard sidebar open (the copied value appears there), then delete the source cell while I'm in it (leaving the format) then pasting in the destination cell by clicking on the clipboard item. That works when recording but the recorded macro returns an error "PasteSpecial methos of Worksheet class failed". While the item is in the sidebar, the actual clipboard has been emptied.

There seems to be some possible workarounds: 1: stop the clipboard being emptied by excel, (what genius came up with the idea of deleting clipboard? I suppose we're just lucky they were working on excel, not windows or office...) 2: somehow paste cut text as "value" or "formula" or "HTML". 3. an extraordinarily long code to set range, copy, paste, reselect range, delete, remove range name, reselect destination range!

Would be very grateful for help with this riddle.

Answer 1

Here's a work around without VBA/Makros:

Instead of selecting and cutting a "whole" cell, select and cut the "content" of the cell (first select the cell, then, for example press F2 and then Ctrl+A). After you use "cut", the cell will be empty but the format won't be changed.

When you paste, your destination cell will keep it's original format but get the value/formula from the source cell. If you want to transfer the format from source to destination just use copy and paste (only pasting the format).

Answer 2

Done it! With help from How to exclude ranges overlap from a range ? (Move cell contents macro)

Sub E____MoveSelectedCellsContentsOnlyKeepFormats_Ctrl_M()

Application.CutCopyMode = False 'clears any existing copy mode
On Error GoTo EXITSUB 'exits if cancel clicked

    Dim RANGE_TO_COPY As Range 'define inputbox variable
    Dim CELL_TO_PASTE_TO As Range 'define inputbox variable

'-----------name SOURCE range = selected before macro started
    Set RANGE_TO_COPY = Selection 'is this necessary, when not using inputbox?
        COPYSOURCE = RANGE_TO_COPY.Address(False, False) 'name the inputbox selection as a range

'=========== inputbox to select PASTE destination
    Set CELL_TO_PASTE_TO = Application.InputBox("select top left cell of range to PASTE TO, with the mouse", Default:=Selection.Address, Type:=8)

'------------- assigns name to the selected DESTINATION range
    PASTERANGE = CELL_TO_PASTE_TO.Address(False, False) 'name the inputbox selection as a range

'=========== action = COPY SOURCE
    Range(COPYSOURCE).Copy

'======================PASTE TO DESTINATION
'DEFAULT: PASTE FORMULAS AND NUMBER FORMATS (MATCHES DESTINATION FORMAT, keeps date/ etc original):

    Range(PASTERANGE) _
    .PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'formulas+number format

'======DELETE SOURCE CELL CONTENTS - remove if COPY required
'------------ loop - from superuser - deletes source but NOT pasterange overlap
Dim rgLoop As Range, rgToDelete As Range
        For Each rgLoop In Range(COPYSOURCE).Cells
            If Intersect(rgLoop, Range(PASTERANGE).Resize(Range(COPYSOURCE).Rows.Count, Range(COPYSOURCE).Columns.Count)) Is Nothing Then
                If rgToDelete Is Nothing Then Set rgToDelete = rgLoop Else Set rgToDelete = Union(rgToDelete, rgLoop)
            End If
        Next rgLoop

        rgToDelete.ClearContents 'deletes contents keeps formatting

EXITSUB:

End Sub

:-)