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

 

[nageswararao]

Try it

On error resume next 
Sheets("Master Sheet").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulas

 

[Project217]

Try it

On error resume next
Sheets("Master Sheet").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulas

Hi,
No It didn't work. just works the first time then error the next.

 

[Project217]

Hi,
No It didn't work. just works the first time then error the next.

Thanks but it didn't work. its weird, It was working before, But not now.

 

[mohadin]

Try

Sub PastTableToMasterSheet()
    ActiveSheet.Unprotect Password:=""
    Range("Table34").Copy
    Sheets("Master Sheet").Unprotect Password:=""
    Sheets("Master Sheet").Range("B" & Sheets("Master Sheet").Cells(Rows.Count, 2).End(xlUp).Row).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]

Try

Sub PastTableToMasterSheet()
    ActiveSheet.Unprotect Password:=""
    Range("Table34").Copy
    Sheets("Master Sheet").Unprotect Password:=""
    Sheets("Master Sheet").Range("B" & Sheets("Master Sheet").Cells(Rows.Count, 2).End(xlUp).Row).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

No, Its still the same. works even second time.

 

[Project217]

Ok, So when I comma out the protection, It works even time, But when the unprotected and protect the ("Master Sheet") it only works every second time, Any thought's on what this could be?

 

[mohadin]

Hi
Got it
This will works for sure

Sub PastTableToMasterSheet()
    ActiveSheet.Unprotect Password:=""
    Sheets("Master Sheet").Unprotect Password:=""
    Range("Table34").Copy
    Sheets("Master Sheet").Range("B" & Sheets("Master Sheet").Cells(Rows.Count, 2).End(xlUp).Row).Offset(1).PasteSpecial -4123
    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]

Hi
Got it
This will works for sure

Sub PastTableToMasterSheet()
    ActiveSheet.Unprotect Password:=""
    Sheets("Master Sheet").Unprotect Password:=""
    Range("Table34").Copy
    Sheets("Master Sheet").Range("B" & Sheets("Master Sheet").Cells(Rows.Count, 2).End(xlUp).Row).Offset(1).PasteSpecial -4123
    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

Thanks very much It does copy and paste every time, However it doesn't clear the table in the active sheet.

 

[mohadin]

Add this line

   Range("Table34").ClearContents

Before this line

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