Excel Macro to Cut, Paste and then delete row

[colinh69]

Hi,

I am using the following macro to cut and paste and then clear contents of a protected sheet but when I try to change it to delete the entire row instead of clearing the contents using "EntireRow.Delete" instead of "ClearContents" I get an error.

I would really appreciate help with this.


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect ("")
Dim lrow As Long, lcell As Long

lrow = Sheet2.Cells(Rows.Count, 18).End(xlUp).Row + 1 'next available row on sheet2
lcell = Sheet1.Cells(Rows.Count, 18).End(xlUp).Row 'last cell in column-J on sheet1

If Target.Cells.Count > 1 Then Exit Sub


If Not Intersect(Target, Range("J2:J" & lcell)) Is Nothing Then
If Target.Value <> vbNullString Then
Range(Cells(Target.Row, 1), Cells(Target.Row, 18)).Copy Destination:=Sheet2.Range("A" & lrow)
Range(Cells(Target.Row, 1), Cells(Target.Row, 18)).ClearContents
End If
End If
ActiveSheet.Protect Password:=""
End Sub

 

[colinh69]

Sorry but is there anyone out here that can help me with this.
Thanks

 

[RoryA]

For future reference, you will generally get better responses if you tell us what error you get and where.
Try this version:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect ""
Dim lrow As Long, lcell As Long

lrow = Sheet2.Cells(Rows.Count, 18).End(xlUp).Row + 1 'next available row on sheet2
lcell = Sheet1.Cells(Rows.Count, "J").End(xlUp).Row 'last cell in column-J on sheet1

If Target.Cells.Count > 1 Then Exit Sub


If Not Intersect(Target, Range("J2:J" & lcell)) Is Nothing Then
If Target.Value <> vbNullString Then
Range(Cells(Target.Row, 1), Cells(Target.Row, 18)).Copy Destination:=Sheet2.Range("A" & lrow)
On Error Resume next
Application.Enableevents = False
Target.EntireRow.Delete 
Application.Enableevents = True
On Error Goto 0
End If
End If
Me.Protect Password:=""
End Sub

 

[AlisonS]

Colin,

This may be irrelevant now after Rory's reply, but I tried your code and it ran fine. I did change some column values to make it fit the data I used, but I didn't get an error. (BTW, J is col 10 not 18.)

Alison

 

[colinh69]

Hi,
Thank you both for your assistance, I used Rory's and it works perfectly.
I have one more thing to ask, if I try to share the workbook it stops working, is there any way around this as I need to share this with eveyone in the office.
Thanks again

 

[RoryA]

No, since you cannot alter the protection in a shared workbook. Additionally, shared workbooks are an abomination and should be avoided if at all possible.

 

[colinh69]

Hi Rory,

Thanks for the quick reply. I gues we will have to work it in the office just using the 1 sheet.

Thanks again