Need VBA code to copy row and insert copied rows

[clintazz]

Hi all,

I have zero experience in VBA, tried to google some solutions yet I can't nail this.

I need to automate a simple process. I am copying rows (rows 127 to 138) then selecting row 143, right clicking and hitting 'insert copied cells'

Can someone please provide me the code for this, i will be 'copying and pasting' this worksheet in the same file and would like this macro to work on the copied worksheets.

Many thanks in advance.

[Mumps1]

Try:

Sub InsertRows()
    ActiveSheet.Rows("127:138").Copy
    ActiveSheet.Rows(143).Insert
    Application.CutCopyMode = False
End Sub

[clintazz]

Hi there,

Many thanks for your prompt reply. It sometimes work and other it doesn't


I'm getting an error Run-time error 1004, copy method of class failed.

I closed excel ran the macro and it ran, when i created a 'box' and assigned a macro to it, it gave me the same error again

[Mumps1]

It is always easier to help and test possible solutions if we could work with your actual file. Can you attach a copy? Explain in detail what you are trying to do.

[clintazz]

This is going to be an extremely stupid question, how do i attached a file here?

[Mumps1]

The only stupid question is the one you don't ask. Click the 'Go Advanced' button at the lower right. Then scroll down a bit and click 'Manage Attachments' and follow the instructions.

[clintazz]

I hope i followed the correct procedure,

Case in point at first the code ran properly but then iwhen i deleted the created rows, it gave me an error. a couple of 'issues' the file is currently protected, password is clintazz. will we be able to do the macro in such a way that it unprotects the worksheet, copies and paste values the amounts in the new rows and then locks it again.

[Mumps1]

I ran this macro, deleted the created rows and ran it again. I did this several times with no errors. Your used range went all the way to column ABX and down to row 836 even though all the extra columns and rows were blank. I deleted the unnecessary rows and columns.

Sub InsertRows()
    ActiveSheet.Unprotect Password:="clintazz"
    ActiveSheet.Rows("127:139").Copy
    ActiveSheet.Rows(145).Insert
    Application.CutCopyMode = False
    ActiveSheet.Protect Password:="clintazz"
End Sub

Try the attached file.