Copying formula to the next row when inserting a new row

Make the area with your data and formulas a Table:

Then adding new information in the next line will copy all formulas in that table for the new line. Data validation will also be applied for the new row as it was for the whole column. This is indeed Excel being smarter with your data.

NO VBA required...

You need to insert the new row and then copy from the source row to the newly inserted row. Excel allows you to paste special just formulas. So in Excel:

• Insert the new row
• Copy the source row
• Select the newly created target row, right click and paste special
• Paste as formulas

VBA if required with Rows("1:1") being source and Rows("2:2") being target:

Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("2:2").Clear

Rows("1:1").Copy
Rows("2:2").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone

One other key thing that I found regarding copying rows within a table, is that the worksheet you are working on needs to be activated. If you have a workbook with multiple sheets, you need to save the sheet you called the macro from, and then activate the sheet with the table. Once you are done, you can re-activate the original sheet.

You can use Application.ScreenUpdating = False to make sure the user doesn't see that you are switching worksheets within your macro.

If you don't have the worksheet activated, the copy doesn't seem to work properly, i.e. some stuff seem to work, and other stuff doesn't ??