Run-Time error '1004': PasteSpecial method of Range class failed

  • Hello,

    I have been having issues with the code below and for the life of me cannot figure it out. It seems to run once, then after that I get the Run-Time error '1004': PasteSpecial method of Range class failed.
    The first part of the code copies the range A1:CC200 from the sheet that the user is working on. It is run from a hyperlink that calls the code. The paste part of the code is pasting to a xlVeryHidden worksheet that is password protected (this is where I get the Error Message). After this code is run I am using the Workbook_SheetDeactivate and the GoToPreviousSheet() code to return the user to the sheet that the code was originally executed from.

    Any help would be greatly appreciated.


    Sub Workbook_SheetDeactivate(ByVal Sh As Object)
        LastSheet = Sh.Name
    End Sub

    Sub GoToPreviousSheet()
    MsgBox ("Returning to the Last Active Sheet..." & LastSheet)
    End Sub
  • I believe the code might work every other time, and then not work the other time.

    Possible when you have the copy command, then have the command to unprotect the sheet. The copy command no longer exists. Try unprotecting the sheet first, then copy and paste

    Sheets("BuyerEmails").Unprotect "Scott"
        Sheets("BuyerEmails").Range("A1").PasteSpecial Paste:=xlValues
        Application.CutCopyMode = False
        Sheets("BuyerEmails").Protect "Scott", DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowInsertingHyperlinks:=True
  • I can appreciate this, however the sheet that gets copied is the sheet the user is working in at the time the code is run and the sheet name can change constantly, that's why my first line of code is the copy. Can I somehow save what's copied as a variable? or can I store the current active sheet name as a variable and use it in the code as you suggest?

    Thank you for your help.

  • Thank you for your help, your comments were absolutely correct. For some reason the copy is lost after you use the unrotect worksheet. As you suggested I simply moved the copy command below the unprotect. Kind of strange it worked once, but not the second time . Thank you for your advise.

    How can I mark this as SOLVED

    Sincerely, Scott

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!