Solved: Can I insert a copied row with a macro?

[deocder]

Hello,

Looking for some help getting my macro to insert a copied row as outlined below. It's probably pretty simple but I am not familiar with the code.

On the attached sheet, I am trying to copy row 24 (which contains formulas and other conditional formatting) and, upon clicking the "Insert Row" button I have linked to the macro, will insert that row below the last filled in row, in this case after row 32. I say insert and not just copy formatting because there is data in row 33 that is needed by the charts. In this case, after inserting the new row, the data in row 33 will be in 34.

[Bob Phillips]

Is this what you want



Sub ADD_ROW()

A = Int(InputBox("How many rows?", "DATA ENTRY"))
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Rows(LastRow).Resize(A, 1).Insert Shift:=xlDown
Rows(24).Copy Range("A" & LastRow).Resize(A, 1)
End Sub

[deocder]

Thanks xld,

This does insert the correct row, but does not shift, in this case, row 33 down. In other words, the macro overwrites the values in row 33 instead of inserting the copied cell.

[Bob Phillips]

It shunts them down for me.

[deocder]

It does this for the value in column A, but I also have values in column X,Y,Z, AC,AD,and AE that do not shift down. Does it shift the values in those columns down for you?

I am using Excel 2003.

[Bob Phillips]

That was just plain dumb!"



Sub ADD_ROW()

A = Int(InputBox("How many rows?", "DATA ENTRY"))
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Rows(LastRow).Resize(A).Insert Shift:=xlDown
Rows(24).Copy Range("A" & LastRow).Resize(A)
End Sub

[deocder]

YES! This is exactly what I want it to do!

Thank you xld!

[deocder]

If I hide row 24, referenced in the code above, the inserted row is also hidden. If I want to keep row 24 hidden, is there a way to unhide the inserted row?

[Bob Phillips]

Sub ADD_ROW()

A = Int(InputBox("How many rows?", "DATA ENTRY"))
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Rows(LastRow).Resize(A).Insert Shift:=xlDown
Rows(24).Copy Range("A" & LastRow).Resize(A)
Rows(LastRow).Resize(A).Hidden = False
End Sub

[deocder]

Exactly, thank you for your help!