Insert 1 Row where needed based on another one.

[olivertweak]

Hi Everyone,

I have an excel sheet, with rows going from column A to U, I use it to track occupancy. My column A contain dates and sometimes I need to enter a new event between two dates so I created an active X control which allows me to insert a row below any selected row.
Example:

A
15-07-14
18-07-14

No let's say an event occurred on the 16-07-14

So I want to insert a row between those 2 dates so that:

A
15-07-14
16-07-14
18-07-14

The problem is that when I insert that row, the formatting is good but the formulas are not present in the cells.
So at the moment, I insert the row where needed, then copy row 5 (a hidden blank row with format and formulas but without data that I use as a reference) and paste the formula in the newly inserted row. So like this I get a fresh new row ready to receive my set of data.

I would like to be able to insert the copy of Row 5 (A5:U5) at any position but only with format and formulas, no data. So far, I had only managed to copy the above row but it populates the new row with the data from above so I kept my old solution.

Below is the code of my activeX control:

Private Sub CommandButton2_Click()
Dim insRows As Long
insRows = ActiveCell.Row
Selection.Insert Shift:=xlDown
Range("AB" & insRows - 1).Select
Selection.AutoFill Destination:=Range _
("AB" & insRows - 1, "AB" & insRows), Type:=xlFillValues
Range("AB" & insRows - 1, "AB" & insRows).Select
Range("AB" & insRows).Select
End Sub

If someone can assist me so that this macro also insert the formulas it would be perfect for me.

Thanks a lot.

[stnkynts]

Could you use .FillDown instead of AutoFill? That should copy the the format and formulas from the row above. If that doesn't work you could always copy the row and then use pastespecial to paste the formulas and formats.

[olivertweak]

Hi stnkynts,

Thanks a lot for helping. I just tried it but it doesn't work. Do you have any suggestions regarding the code? I'm a newbie and I'm still trying to figure out certain things.
Is there a way for me where each time I insert the row, it will take A5:U5 as a reference? This might be an easier option I think.

[mikerickson]

try putting something like this in the sheets code module.

Please Login or Register  to view this content.

[olivertweak]

Hi Mikerickson,
Thanks a lot for assisting me with my little challenge. Unfortunately it is not working. What I exactly want to do is to enhance the function of my Active Control. (Command_Button2) which at the moment can:
• insert a row wherever I want with the same formatting
I just would like to keep the formulas as well as I insert that row, just without copying any value.
I really appreciate your help thank you.