Macro initially has runtime error 1004 but works on 2nd attempt

[Harls]

Hello All

Please could I request a little assistance with a macro I have cobbled together. I have it linked to a button & initially it has a runtime error 1004, but will then work on 2nd attempt. I have the worksheet protected & in the vba, I unprotect it & then protect it again but if I run it without the sheet being protected it works fine which leads me to believe the problem is to do with the protection. My code is as follows

[code]
Sub Retrieve_Ref_BOM()
Range("J13:M14").Select
ActiveCell.Hyperlinks(1).Follow
Sheets("COMPLETE BOM").Select
ActiveSheet.Unprotect
Columns("P:V").Select
Selection.EntireColumn.Hidden = False
ActiveSheet.Range("$A$6:$U$3000").AutoFilter Field:=17
Range("A3:L3000").Select
Selection.Copy
ActiveWorkbook.Windows(1).Visible = False
ActiveSheet.Unprotect
Range("G24:R3021").Select
ActiveSheet.Paste Link:=True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingHyperlinks:=True, AllowFiltering:=True
Range("J13:M14").Select
ActiveCell.Hyperlinks(1).Follow
Application.CutCopyMode = False
ActiveSheet.Range("$A$6:$U$3000").AutoFilter Field:=17, Criteria1:="TRUE"
Columns("Q:U").Select
Selection.EntireColumn.Hidden = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
ActiveWorkbook.Save
ActiveWindow.Close
Filter_BOM
ActiveSheet.Range("M16:M17").Select
End Sub
[code/]

I also then has this in the Thisworkbook section

[code]
Private Sub Workbook_Open()
Sheet1.Unprotect
Sheet1.Protect , Userinterfaceonly:=True, AllowInsertingHyperlinks:=True, AllowFiltering:=True
Sheet1.EnableSelection = xlUnlockedCells
Sheet1.EnableOutlining = True
End Sub
[code/]

When first run the error comes up & debugs on the line ActiveSheet.Paste Link:=TRUE

Any assistance with this would be greatly appreciated

Many Thanks in advance

[TMS]

I suggest you take out all the Protect and Unprotect lines of code in the main subroutine. Just leave the Workbook Open code.

[Harls]

Yep that sorted it, thank you so much, I thought it would be something relatively simple, shows my vba in-experience lol

Again Thank you TMS

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.