Excel VBA: How do I PasteSpecial into same row of Cell location after Cell is changed?

I want to have a specially formatted/formulated row (from a template worksheet) pasted onto the same row that's being modified on the main worksheet. This is what I have so far, but getting a run-time error 1004:

"PasteSpecial method of Ranged class failed"

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A5:A10000")

    'the template of a very long formatted row with formulats
    Set TemplateRow = ActiveWorkbook.Worksheets("Templates").Range("A1:BB1")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
       Is Nothing Then

       TemplateRow.Copy
       Range(Target.Address).PasteSpecial Paste:=8
       Range(Target.Address).PasteSpecial Paste:=-4104
       Application.CutCopyMode = False
    Else
        Range(Target.Address).EntireRow.Delete
    End If
End Sub

Answers


Can you try this for me? (UNTESTED)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range, TemplateRow As Range

    If Target.Cells.CountLarge > 1 Then Exit Sub

    On Error GoTo Whoa

    Application.EnableEvents = False

    Set KeyCells = Range("A5:A10000")

    Set TemplateRow = Worksheets("Templates").Range("A1:BB1")

    If Not Intersect(Target, KeyCells) Is Nothing Then
        TemplateRow.Copy
        Target.PasteSpecial Paste:=8

        DoEvents

        TemplateRow.Copy '<~~ Insurance against clipboard getting cleared
        Target.PasteSpecial Paste:=-4104
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Solution by OP.

Resolved thanks to Siddharth Rout.

Here is the full modified code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range, TemplateRow As Range
    Dim ArticleData As String

    If Target.Cells.CountLarge > 1 Then Exit Sub

    On Error GoTo Whoa

    Application.EnableEvents = False

    Set KeyCells = Range("A5:A10000")

    Set TemplateRow = Worksheets("Templates").Range("A1:BB1")

    If Not Intersect(Target, KeyCells) Is Nothing Then
        ArticleData = Range(Target.Address).Value
        If ArticleData <> "" Then
            TemplateRow.Copy
            Target.PasteSpecial Paste:=8

            DoEvents

            TemplateRow.Copy '<~~ Insurance against clipboard getting cleared
            Target.PasteSpecial Paste:=-4104
            Range(Target.Address).Value = ArticleData
        Else
            Range(Target.Address).EntireRow.Delete
        End If
    End If

    Letscontinue:
        Application.EnableEvents = True
        Exit Sub
    Whoa:
        MsgBox Err.Description
        Resume Letscontinue
End Sub

Need Your Help

Order of execution SQL

mysql sql

This is hypothetical code:

Pass a foreign key to partial render in rails

ruby-on-rails database render partial

I'm trying to pass the foreign key from one view to another using partial render. However there is a problem in passing the local variable because only the last record get to be displayed.