CODEWITHSUNDEEP
excelvbaruntime-error
Lorthas
Lorthas

Reputation: 376

Runtime Error 1004 on the second macro execution

It Used to work well before protecting the sheet

I searched for a similar problem here in SO but didn't solve my problem. I don't know if it is specific to my code.

I have a sheet protected and all cells locked except for one Range which I call R. I copy the content From R to R2 but I need to unprotect the sheet first. Then I copy all from R to R2 then Lock and protect the sheet again. As I said the second time I run the macro, it throws a 1004 error and can't figure out why. The code worked well until I protected the sheet all the time. Here is the code:

It throws the error in the line where it says

.PasteSpecial Paste:=xlPasteAll

Runtime Error 1004 on the PasteSpecial method of the .Range Class

Sub CopyPapers(ByRef R As Range, R2 As Range)
    With R2
        R.Copy
        Hoja1.Unprotect
        .PasteSpecial Paste:=xlPasteAll
        .Locked = True
        Hoja1.Protect
    End With

    R.Cells(2, 1).Activate
    Application.CutCopyMode = False
End Sub

R and R2 are ranges of cells. Whenever I update the content in R, that content and formats are copied over to R2. For that I use the Worksheet_Change() event and I check wheter only the range (R) is modified using Intersect() method.

Edit: I ignore why but moving R.Copy instruction under Hoja1.Unprotect seems to solve the problem. If anyone can explain why that it I'd appreciate it.

Upvotes: 0

Views: 70

Answers (1)

HTH
HTH

Reputation: 2031

the calling of Unprotect method cancels the Copy command previuosly issued, so the subsequent PasteSpecial fails

BTW the correct statements sequence should be

Sub CopyPapers(ByRef R As Range, R2 As Range)
    Hoja1.Unprotect
    R.Copy
    With R2
        .PasteSpecial Paste:=xlPasteAll
        Application.CutCopyMode = False
        .Locked = True
    End With
    Hoja1.Protect

    R.Cells(2, 1).Activate
End Sub

in order to

  • Unprotect first

  • populate With .. End With block with statements that belong to it only

  • run Application.CutCopyMode = False as soon as possible

And yet there should be some changes to do in order to check that R and R2 ranges do belong to Hoja1 sheet

for instance you could use R.Parent.Unprotect and R.Parent.Protect to ensure you're acting on the sheet to which R belongs. And still assuming R2 belongs to it too...

Finally, be sure your Worksheet_Change() event handler doesn't call itself recursively, disabling Application.EnableEvents before the call to CopyPapers() and enabling it before exiting

Upvotes: 1

Related Questions