Insert rows in Excel 2007

[Zahhhaaaa]

Hello, I have a problem, I know it's not a big problem but I don't get it :P

I'm using

Sub insertrow()
ActiveSheet.Unprotect Password:="1"
With Rows(6)
    .Copy
    .Insert 'the copy will be put in the newl;y created row
End With
Application.CutCopyMode = False
ActiveSheet.Protect Password:="1"
End Sub

to copy row 6 and insert a copy of it below.

My problem is that the new row appears ABOVE, not below where I wanted it. What's wrong with the code?

PS. Range a6:d6 is what needs to be copied. Is it possible to select only this range, not the whole row? (but this is not necessary)

 

[Joe4]

Here's one way:

Sub MyInsertRow()
 
    ActiveSheet.Unprotect Password:="1"
    Rows(7).EntireRow.Insert
    Range("A6:D6").Copy
    Range("A7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Protect Password:="1"
    
End Sub

 

[Zahhhaaaa]

Thank you soooooo much!

 

[Zahhhaaaa]

Joe4, just curious, is it possible that when I copy this row a6:d6 and it appears on a7, that when I copy row a6 again, it appears on a8, and so on ?? That every new row appears below previous copy?

Code works perfectly, but if it's possible to have this little thing added in code you gave, I would approciate it very much!

 

[Joe4]

If you can determine the logic for doing this, then yes.

Maybe you can place some sort of indicator in one of the unused columns that indicates that row was inserted, so when the code it runs, it will place it below all those values (i.e. place an "i" in column Z).

Or, if it is all being done within a single run of VBA code, you can add a loop counter to your code to keep track of how many rows you have added, and incorporate that into the insert code.