copy formulas to end of file

I assume by "end of the file" you mean the last row of a dynamic range. Insert this code into your macro to have a formula appear in column B starting with B2, down as many rows as there is contiguous data in column A starting with A2, modify as needed:

Application.ScreenUpdating = False
Sheets("YourSheetName").Select
Dim LastRow As Long
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
Range("B2:B" & LastRow).FormulaR1C1 = "YourFormula"
Application.CutCopyMode = False
Application.ScreenUpdating = True

Tom Urtis

This is great! How would it work with more than one formula to copy? Can an array be accomplished by selecting the array? I appreciate the help as this is new to me.

: Can someone tell me how to copy a formula to the end of the file withour using an absolute range. The spread sheet I'm working on may have 200 rows one week and 300 the next. I'd like the a formula copied to then last row no matter what the last row may be.

Rich --

In answer to your first question, it could work with more than one formula, just use this block of code per formula and modify the range (and of course the formula too). Keep in mind that if you have to do more than one formula auto-fill in the same procedure, you cannot declare duplicate variables (in this case "Last Row" as Long), so you will need to rename the "Last Row" to something else.

I'm unclear about your second question. It's possible to place an array formula in a procedure but the idea of arrays in many cases is to have one array formula do the work of, and be put in place of, lots of other formulas. So copying an array might be counterproductive, and could slow your system as these formulas tend to recalculate slowly. Also, arrays refer to ranges that cannot change, so it's risky to place arrays in code that deals with dynamic ranges.

I'd advise that you do some experimenting with this code and with arrays in the code on a mock spreadsheet, before committing to a final spreadsheet presentation (especially if you're doing it for your boss!).

Good luck!!!

Tom Urtis

This is great! How would it work with more than one formula to copy? Can an array be accomplished by selecting the array? I appreciate the help as this is new to me. : I assume by "end of the file" you mean the last row of a dynamic range. Insert this code into your macro to have a formula appear in column B starting with B2, down as many rows as there is contiguous data in column A starting with A2, modify as needed