Runtime error 1004

[All2Cheesy]

Hi guys,

I've written a Macro that will copy what's in cells and then insert that X amount of times in the cells below(Being used to copy formulas). This works perfectly once I've unlocked the sheet, however, I receive the following error when trying to run this macro in a locked sheet: Runtime Error '1004' Insert method of range class failed.

I've coloured the section that highlights in debug mode in my code below. It is also critical that the sheet remains locked. Thank you in advance!

Sub AddLines()

Application.Calculation = Manual
Range("A5023:AG5023").Copy
[COLOR="#FF0000"]Range("A5023:AG5023").Resize(Range("Q7").Value).Insert Shift:=xlDown[/COLOR]
Application.Calculation = xlAutomatic

    Range("A5024:G65000").Select
    Selection.ClearContents
    Range("J5024:L65000").Select
    Selection.ClearContents
    Range("Q5024:T65000").Select
    Selection.ClearContents


End Sub

 

[Michael M]

The sheet will have to be unlocked unless you use the UserInterfaceonly method, which has to be applied at workbook-open and need to reference the sheet in question
See here for more info

Excel VBA: Macro Code To Run Macros On Protected Worksheets & Sheets

Sub AddLines()
Application.Calculation = Manual
Range("A5023:AG5023").Copy
Range("A5023:AG5023").Resize(Range("Q7").Value).Insert
Application.Calculation = xlAutomatic
    Range("A5024:G65000").ClearContents
    Range("J5024:L65000").ClearContents
    Range("Q5024:T65000").ClearContents
End Sub

 

[All2Cheesy]

The sheet will have to be unlocked unless you use the UserInterfaceonly method, which has to be applied at workbook-open and need to reference the sheet in question
See here for more info

Excel VBA: Macro Code To Run Macros On Protected Worksheets & Sheets

Sub AddLines()
Application.Calculation = Manual
Range("A5023:AG5023").Copy
Range("A5023:AG5023").Resize(Range("Q7").Value).Insert
Application.Calculation = xlAutomatic
    Range("A5024:G65000").ClearContents
    Range("J5024:L65000").ClearContents
    Range("Q5024:T65000").ClearContents
End Sub

Ah okay.

Thanks for that.

 

[All2Cheesy]

Found a work around in case you were interested.

Unlocking and then re-locking the sheet within the macro gets the job done a tad quicker. Thanks for the assist though!

Sub AddLines()

'Unlock Sheet
ActiveSheet.Unprotect Password:="PASSWORD"

Application.Calculation = Manual
Range("A5023:AG5023").Copy
Range("A5023:AG5023").Resize(Range("Q7").Value).Insert
Application.Calculation = xlAutomatic
    Range("A5024:G65000").ClearContents
    Range("J5024:L65000").ClearContents
    Range("Q5024:T65000").ClearContents
    
'Lock Sheet
ActiveSheet.Protect Password:="PASSWORD"

End Sub

 

[Michael M]

Ummm....I would have suggested that....but you stated

It is also critical that the sheet remains locked