Copy from one sheet, paste in another, delete old row.

[supernam]

I could really use some help. Hopefully someone can dumb this down enough for me to understand.

When making the entry that would execute the data transfer I get the following error, Pastespecial method of range failed, and its in reference to
Sheets("Closed Discrepancies").Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValuesAndNumberFormat

If I change it to the xlPastevalues I get the following error, Delete method of range class failed for the following line
Range("A" & Target.Row).Resize(, 13).Delete Shift:=xlUp




Complete code is below.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("M:M")) Is Nothing Then
If UCase(Target.Value) = "Y" Then
Range("A" & Target.Row).Resize(, 13).Copy
Sheets("Closed Discrepancies").Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValuesAndNumberFormat
Range("A" & Target.Row).Resize(, 13).Delete Shift:=xlUp
End If
End If
Application.EnableEvents = True
End Sub


Any and all help would be greatly appreciated.

Thank you so much for taking the time to look into what I have going on!

 

[Fluff]

It should be

xlPasteValuesAndNumberFormats

note the s at the end.

 

[supernam]

Thank you, I corrected that and still get an error.


Delete method of range class failed for the following line
Range("A" & Target.Row).Resize(, 13).Delete Shift:=xlUp




Complete code is below.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("M:M")) Is Nothing Then
If UCase(Target.Value) = "Y" Then
Range("A" & Target.Row).Resize(, 13).Copy
Sheets("Closed Discrepancies").Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValuesAndNumberFormats
Range("A" & Target.Row).Resize(, 13).Delete Shift:=xlUp
End If
End If
Application.EnableEvents = True
End Sub

 

[Fluff]

Is that sheet protected?

 

[supernam]

After further review, I did have a sheet protected and then I was able to determine that the table on the sheet was preventing me from running the code. When the code runs though, it overwrites the data that is present. It does not continue adding rows. It simply puts it where it had previously depoisted the previous data. How to I get it to add another row vice dropping it on existing data?

 

[Fluff]

Does column A on the "Closed Discrepancies" sheet always have data?

 

[supernam]

Does column A on the "Closed Discrepancies" sheet always have data?

After your suggestion I changed the target row to B and all seems to be well in the world. I really appreciate your help. Thank you.

 

[Fluff]

Glad you sorted it & thanks for the feedback.