Search code examples
excelcellcopy-pastevba

copy and paste on excel without deleting cells current content

I am trying to create a cell to build canned response emails in excel. I need to be able to paste text into a cell without deleting the information that already exists within that cell (and I would need to be able to do this repeatedly.) I am basically asking for help creating a single cell that has similar 'copy and paste' properties as a word doc. i.e. when I paste text into a cell it will insert the new text below previous text rather replacing the existing text with clipboard.

I could definitely use some help coding this in VBA. Please let me know if anything needs to be clarified! Thank you

Solution

  • I wrote the code to do what you asked for, but I'm not sure if it is really what you need.

    Press Alt+F11

    Double click on the worksheet you want this to work on (Left Side)

    Paste Code

    Paste in this code.

    Note that this code will allow you to clear a cell by hitting Delete.

    Otherwise, it would be difficult to delete it.

    Note that this will essentially append any cell that you try to edit with your edit.

    Dim currentVal
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If currentVal <> vbNullString And Target.Value <> vbNullString Then
    Application.EnableEvents = False
    Target.Value = currentVal & vbCrLf & Target.Value
    currentVal = Target.Value
    Application.EnableEvents = True
Else
    currentVal = Target.Value
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge = 1 Then currentVal = Target.Value
End Sub

    How it works:

    Working

    Hopefully final edit to work with merged cells typing on mobile unable to test:

    Dim currentVal
Private Sub Worksheet_Change(ByVal Target As Range)
If currentVal <> vbNullString And Target.Cells(1,1).Value <> vbNullString Then
    Application.EnableEvents = False
    'Use this to add an extra line instead
    Target.Cells(1,1).Value = currentVal & vbLf & vbLf & Target.Cells(1,1).Value
    'Target.Cells(1,1).Value = currentVal & vbLf & Target.Cells(1,1).Value
    currentVal = Target.Cells(1,1).Value
    Application.EnableEvents = True
Else
    currentVal = Target.Cells(1,1).Value
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
currentVal = Target.Cells(1,1).Value
End Sub