Excel VBA Function throws an error when changes the cell

Question

I have been using this code which copies the range and paste the range as picture but when i change the concerns cell it throws an error that is Error 1004, Microsoft Excel cannot paste the data.

Any help will be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C5:P5")) Is Nothing Then

Application.CutCopyMode = TRUE

ActiveSheet.Pictures.Delete

Worksheets("Pivot").Range("FC3:FP35").Copy

With Worksheets("Map")
.Activate
.Range("C8").Select
.Pictures.Paste
End With

Application.CutCopyMode = FALSE

End If
End Sub

score 0 · Accepted Answer · answered Jun 23 '21 at 12:14

You need to turn off events Application.EnableEvents = False before changing cells and turn them on after. Make sure they get turned on in any case of an error in this event or you will not be able to fire any other events in your Excel instance. So error handling in this event is a must have. • You might benefit from reading How to avoid using Select in Excel VBA.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("C5:P5")) Is Nothing Then
        On Error Goto ERR_ENABLE_EVENTS
        Application.EnableEvents = False

        Me.Pictures.Delete

        Worksheets("Pivot").Range("FC3:FP35").Copy Destination:=Worksheets("Map").Range("C8").Paste
    End If

ERR_ENABLE_EVENTS:
    Application.CutCopyMode = False
    Application.EnableEvents = True
    If Err.Number <> 0 Then
        Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
    End If
End Sub

Thank you the error handling now its working. – Jun 23 '21 at 13:18 — , Jun 23 '21 at 13:18

Excel VBA Function throws an error when changes the cell

1 Answers1