CODEWITHSUNDEEP
excelvba
Eliyahu
Eliyahu

Reputation: 158

VBA code to copy a selection just as cut does, so that paste doesn't modify references?

When I copy and paste, then excel moves the cell references down. E.g. References to cell B2 on line 3 become references to line B3 on line 4.

With cut and paste, this doesn't happen, and references to cell B2 remain references to B2.

I want the behavior of the cut and paste, but without losing the original content.

I've tried Recording a Macro, then pressing F2, SHIFT-HOME, copy and escape, and excel records absolutely nothing. But this is what I want the macro to do, so that when I copy it, it copies it perfectly.

Upvotes: 0

Views: 313

Answers (2)

Eliyahu
Eliyahu

Reputation: 158

After a lot of work, here's what I came up with:

Dim formulas() As String
Dim initCopyCol As Integer
Dim initCopyRow As Integer
Dim columns As Integer
Dim rows As Integer

Sub copyFormulaRange()

    initCopyCol = Selection.Column
    initCopyRow = Selection.row

    columns = Selection.columns.Count
    rows = Selection.rows.Count

    ReDim formulas(1 To columns, 1 To rows)

    For i = 1 To columns
        For j = 1 To rows
            formulas(i, j) = Cells(initCopyRow + j - 1, initCopyCol + i - 1).Formula
        Next j
    Next i

End Sub

Sub pasteFormulaRange()

    Dim initPasteCol As Integer
    Dim initPasteRow As Integer

    initPasteCol = Selection.Column
    initPasteRow = Selection.row

    For i = 1 To columns
        For j = 1 To rows
            Cells(initPasteRow + j - 1, initPasteCol + i - 1).Formula = formulas(i, j)
        Next j
    Next i

End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96763

Put the following in a standard module:

Dim sForm As String

Sub ccopy()
    sForm = ActiveCell.Formula
End Sub

Sub ppaste()
    ActiveCell.Formula = sForm
End Sub

Then assign the shortcut key Ctrl + e to ccopy and the shortcut key Ctrl + f to ppaste

This should simulate Copy/Paste, but leave cell references intact.

This can be modified for multiple cells.

EDIT#1:

This new ppaste will handle the case of:

single cell -> multiple cells

Sub ppaste()
    Dim r As Range
    For Each r In Selection
        r.Formula = sForm
    Next r
End Sub

Upvotes: 2

Related Questions