Autofill down cell VBA-CodePudding

I am quite new to VBA. Any help will be most welcome as I have been stuck for a few days on this.

I am trying to basically a drag and drop and adding a row as needed to make the spreadsheet neat and tidy.

So far I have managed to do this but it just keeps inserting entire rows rather than moving down.

It is a dynamic entry, I will not always have 2 or 3 new rows. Sometimes it will be 2 some others will be 100

ActiveSheet.Range("A15:AB15").Select
Selection.FillDown
Do Until ActiveCell.Value = 0
    Selection.FillDown
    ActiveCell.EntireRow.Insert
Loop

So I need the loop to drag and drop some formulas I have on a row above then insert a new, drag and drop again and continue until it meets the value 0.

I hope the explanation helps.

Thanks in advance

CodePudding user response：

I'm going to try to answer the question I think you are asking and you can offer clarification if I have misunderstood. Let's suppose you have the following two worksheets in a workbook.

Sheet "Data" has inventory levels for given dates. The values in Columns B through D of that sheet start at some number and eventually reach zero as we move down the column.

On the sheet named Summary, you have a a row of formula that refer to the cells atop the list of values and you want to copy that formula down until the value of the formula copied in each column is equal to 0.

Here is a VBA macro that will do that:

Sub copy_all_down_to_zero()
    Dim cell As Range
    For Each cell In Worksheets("Summary").Range("b2:d4")
        copy_formula_down_to_zero cell
    Next
End Sub


Sub copy_formula_down_to_zero(cell As Range)
    Do Until cell.Value <= 0
        cell.Offset(1).Formula2R1C1 = cell.Formula2R1C1
        Set cell = cell.Offset(1)
    Loop
End Sub

Just configure "copy_all_down_to_zero" to refer to range that holds the initial formulas and run it.