Solved Macro to auto fill cells below, but stay connected?

May 25, 2016 at 12:52:25
Specs: Windows 7
Hello! I have a spreadsheet for event tracking, but there need to be multiple rows/ event so there is one row per participant. Some information needs to remain constant, like the event date, time, etc, but some needs to be tailored to the participant, like type of participant, prep work to be done, etc.

However, the problem is that if a "constant" detail were to change, not all the entries would be updated. The macro I had is

Sub InsertRow()
Dim i As Long
Selection.Offset(1, 0).EntireRow.Insert
i = Selection.Row
Range("A" & i & ":I" & i + 1).FillDown
End Sub

which worked fine, but I want it to somehow fill with =Rowabove, or be linked somehow. Is this possible?


See More: Macro to auto fill cells below, but stay connected?

Report •


#1
May 25, 2016 at 15:17:30
I don't understand what you are trying to do.

AFAICT, your code basically inserts one row below the selected cell/row and copies the data from A:I in the row above to the new row.

What is it that you want it to do?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 26, 2016 at 05:46:07
I basically want rows A-I to be populated with links to the row above, so that when something is changed in the "master row" it changes in all the others as well, but only for columns A-I

Report •

#3
May 26, 2016 at 06:38:10
✔ Best Answer
Try this:

Sub FillDownFormula()
Dim i As Long
  Selection.Offset(1, 0).EntireRow.Insert
    i = Selection.Row + 1
    Range("A" & i & ":I" & i).FormulaR1C1 = "=R[-1]C"
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 26, 2016 at 07:03:20
Thank you thank you thank you!!

Report •

Ask Question