Copy and paste with unlock and lock worksheet

[Project217]

Hi all.

Im Just having some trouble with the below VBA, works the first time perfectly but every second time it crashes and comes up with run time error 1004 PasteSpecial method of range class failed.
Also, I'm looking to add to this macro an email alert with just text only,
Any help would be awesome.

Sub PastTableToMasterSheet()
ActiveSheet.Unprotect Password:=""
Range("Table34").Copy
Sheets("Master Sheet").Unprotect Password:=""
Sheets("Master Sheet").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulas
Sheets("Master Sheet").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=""
Application.CutCopyMode = False
Rows("26:26").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.DELETE Shift:=xlUp
Range("B12").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=""
MsgBox "Order Sent"
End Sub

 

[Project217]

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=""

Add this line

   Range("Table34").ClearContents

Before this line

  ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=""

Thank you, Yes it works, However it just clears the table and starts a new row below the cleared or blank rows, is there a way to start back on row 26 and also not clear the contents or row 25, (This is the first row in the table and I need the contents for a conditional format on the master sheet.)

 

[Project217]

Add this line

   Range("Table34").ClearContents

Before this line

  ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=""

Hi. Thanks very much, it worked, However it clears the table leaving blank rows in the table then new data is added to a new row, so the copy to master sheet copy's the blank rows. Also I need to have in NEW DATA BELOW in row 25 on the table for conditional formatting on the master sheet. any idea how this could be achieved?