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


Board Regular
Jun 11, 2005

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 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 :unsure: , 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.

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
bovinda said:

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.
Upvote 0
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?
Upvote 0

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.
Upvote 0
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.
Upvote 0
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)
.Offset(-2).Resize(, 255).AutoFill _
Destination:=.Offset(-2).Resize(2, 255)
End With
Application.EnableEvents = True
End Sub
Upvote 0
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?


Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back