VBA Run-time error '1004': PasteSpecial method of Range class failed on 2nd run

Can anyone please try to look at my code. I can't figure out the error

At first run, everything is perfect. But on the second run, it will show the run-time error. I need to close & save the 2nd workbook before the macro will work again.

Please help! Thanks in advance.

Please Login or Register  to view this content.

Originally Posted by JosephP

you are using a different password to unprotect the 'sign on & briefing' sheet than the one you protect it with

Hi JosephP,

Yeah, that one is a type-error only since I just changed the actual password with what I'm using. But I already checked my passwords, still it will run the first time then on the 2nd time it will show the run-time error.

Can you please tell me where else is wrong in my code? Thanks a lot!!!

Revised:

Please Login or Register  to view this content.

Hi Winon,

I tried placing the On Error Resume Next on my code. It does remove the error but it doesn't copy/paste the values anymore on the 2nd workbook ("Sign On Consolidated Report for Admin.xlsm"). I have highlighted the line for the run-time error. It seems that the problem exists on the 2nd workbook.

Please Login or Register  to view this content.

Originally Posted by JosephP

try opening the other workbook before you perform the copy

Tried it but the error appears already everytime I run the macro of the first workbook. Unlike earlier, it will run the first time then errors the 2nd time. So to have it working again, I need to save & close the 2nd workbook that opened then re-run the macro again.

It seems that I need to save & close the 2nd workbook at the 2nd run to make it work again. And that makes it frustrating.

By the way, I cannot remain the 2nd workbook open because it's in a network drive. And regular users are normally not allowed to access it since it shows the calculation of the working hours of staff. This is why I need it to be closed again after the pasting the details on the 2nd worksheet.

Hi JosephP,

I really thank you for your time in helping me out with this. Unfortunately, the problem still persist.

I just noticed, if i'll remove the protection of 2nd worksheet, everything seems to be working well regardless how many times i'm running the macro. But my concern now is, if the 2nd worksheet cannot be protected with my current code, it's going to be vulnerable since it's located in a network drive and that any staff who knows the location can access it.

I just can't seem to understand the relevance in pasting values to unprotecting/protecting the workbook.

Any suggestion?

YES! Finally!!!

Thank you so much for your time and expertise, JosephP!

So the error lies in the part:

Please Login or Register  to view this content.

Thanks again for the big help!