Seeking a macro to insert rows with formulas (I think)

[bovinda]

Hello,

I am wondering if anyone would have any suggestions for this, because I am a moron at anything programming/scripting-related. Additionally, my school keeps me too busy to significantly explore how to do it myself...so I humbly come seeking your help.

Basically, I am seeking a VBA script/macro (is there a difference?) that would insert rows into a worksheet and copy the formulas from the preceding row. By formulas, I mean IF statements. So for example, if cell T85 has the formula =IF(F85="Eat In",D85,"") in it, and I insert a row into 86, then T86 would contain =IF(F86="Eat In",D86,"").

I used to have a macro that did just that (that a Mr Excel member named jindon had generously written for me, in fact), and had it assigned to a custom button and everything, but then my computer crashed. I recovered the files that I had used the macro in, but the macro itself seems to be gone.

I've searched and found many posts on this subject, but the macros often seem very specific, and I'm just not that swift , so I thought I'd seek out individual guidance from any who care to offer it.

I am using Excel 2003 and Windows XP w/ SP2.

Thank you for any and all suggestions! Please let me know if I can provide any more information.

 

[poorwallace]

bovinda,

Is the macro that Jindon wrote different from the one in your previous post?

http://www.mrexcel.com/board2/viewtopic.php?p=729499&highlight=#729499

Let's hope that post has the macro you're looking for...other than that, you could manually hit CTRL + D when you insert a row...

my 2 cents..

 

[Aladin Akyurek]

...

I am using Excel 2003 and Windows XP w/ SP2.

...

Then you have exactly what you need:

Select the area with the data and related formulas and convert it to a list by means of Data|List|Create List. Formula copying will now occur automatically.

 

[bovinda]

Hi poorwallace, unfortunately it was a different one. He'd e-mailed it to me personally after we'd discussed it for awhile. Also, the control+D did copy the formula, but the problem is there are probably at least 20 formulas to be copied for each row, each time I insert a new row. Good thoughts though, and thank you for the suggestions.

Hi Aladin, thank you, that is a good suggestion, but because of how I have set the sheets up there are literally hundreds and hundreds of places where I would have to do that for it to be effective. I will keep that in mind in the future, however, if I redesign the work-sheets.

Anybody else have any suggestions?

 

[poorwallace]

bovinda,

When you insert a row, highlight the empty row (by highlighting the row number on the left of the screen) and then push CTL+D.

 

[bovinda]

Hey poorwallace, that's a great suggestion! It does the trick. That will definitely work for now. Thank you!

I think in the long run, because I will be doing this so many times, it may be easier if I can still find a macro to simplify it into a single click (lazy, I know).

So if anyone has any suggestions for this, in terms of macros, let me know still.

 

[bovinda]

Bump! Still looking for a macro for this...

EDIT: Got it! Jindon was kind enough to resend his original work to me. For anyone else who might ever want it, I will post it here. Again, props to jindon for the great work.

Sub InsertRow()
If Selection.Row <= 6 Then Exit Sub
Application.EnableEvents = False
With Cells(Selection.Row, 2)
.EntireRow.Insert
.Offset(-2).Resize(, 255).AutoFill _
Destination:=.Offset(-2).Resize(2, 255)
End With
Application.EnableEvents = True
End Sub

 

[Kinsdono]

i have a quesion to build on this query. I have a workbook that contains data that changes daily. What i have created is a unique identifier. i wanted to be able to create a macro that searches another Tab for the unique idetifier and bring back the matching data, but my issue i am having difficulties with is if there is a new unique identifier how do i go about establishing it is new and then inserting a row from the tab i am running the macro from?

Thanks,

Kinsdono