Thread: Troubleshooting script to paste row value

So the current spreadsheet I have, there is a script which, when the cell in column J is "Yes", will unprotect the sheet, unhide a column, select the current row, copy it, paste as values, hide the row and protect the sheet.

Here is the code in question.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("J5:J1999")) Is Nothing Then Exit Sub
    If Target.Value = "Yes" Then
        ans = MsgBox("Is this entry completed, and are all details correct?", vbYesNo, "Are you sure?")
        If ans = vbNo Then Target.ClearContents
        End If
        If ans = vbYes Then
        'Do Code
        ActiveSheet.Unprotect Password:=""
        Application.EnableEvents = False
        Columns("A:A").Select
        Selection.EntireColumn.Hidden = False
        Rows(Target.Row).Select
        Rows(Target.Row).Copy
        Rows(Target.Row).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        Columns("A:A").Select
        Selection.EntireColumn.Hidden = True
        Application.EnableEvents = True
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True, Password:=""
    End If
End Sub

The problem is, if the sheet is filtered, the code will not work. It gives me

Run-time error '1004':
The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following:
* Click a single cell, and then paste.
* Select a rectangle that's the same size and shape, and then paste.

I have inserted an ActiveSheet.ShowAllData and that works as a bandaid solution, however to make the code run more efficiently (and no need to re-filter), I was wondering whether anyone could tell me how to change the code so that it selects individual cells in the target row, instead of the whole row. The whole row doesn't need to be copy-pasted as values.

bump time...