VBA to drag formulas from row above when dynamically adding in variable number of rows

[Cantrecallmyusername]

Hi,

I have a process which I have been able to update with some help on this forum where data is added from a report to a master sheet. It will dynamically check to see if there are any new names and depending on the number will add the number of rows required above a certain text value. I then past in these names in the newly created rows;
The sheet which the updates are required is the Details tab

This creates the rows needed;

Sub Add_Row_4Resources()
'Adds a number of rows to the Details tab based on the names found on the Actuals report for new additions
  With Worksheets("Detail")
    For j = 1 To Worksheets("Workings").Range("E2").Value
      .Rows(Application.Match("Uplift", .Range("A:A"), 0)).EntireRow.Insert
    Next
  End With
End Sub

This adds the data in the rows created above;

Sub Add_Resource_Name()

    Sheets("Workings").Select
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$F$500").AutoFilter Field:=2, Criteria1:=RGB(255, _
        199, 206), Operator:=xlFilterCellColor
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Detail").Select
    
    Range("A6").Select
    If ActiveCell.Offset(1, 0).Value = "" Then
    ActiveCell.Offset(1, 1).Select
Else
    ActiveCell.End(xlDown).Offset(1, 0).Select
End If
    ActiveSheet.Paste
End Sub

I know require a further step to the above - I need to find the last row in column B and drag the formulas from B (last cell in the column) - JN(last cell in the column)
The number of rows that will need to be dragged down will be ""Workings").Range("E2").Value"

What would be the best way to approach this? The number of rows that the formulas will need to go down will vary and there is a blank line always where the data will stop (not sure that helps)
Thanks in advance.

 

[Cantrecallmyusername]

I have been able to get the formulas to fill from using the below Code

    Dim LR As Long
    LR = ActiveSheet.UsedRange.Rows.Count
    Range("B6:JN6").AutoFill Destination:=Range("B6:JN" & LR)

This pulls all my formulas down from the required rows however I would like to control the autofill to a specified row and not the last row. I have a text value in Column A with the text "Uplift" - I want the rows auto fill to be the row above this - currently I capture this row in Cell B2 - this gives me the row number - how can incorporate this into the above code?

I assume we can dynamically y define LR to reference this however I not sure how to do this.
Any help greatly appreciated.

 

[Deek6t8]

I believe your problem would be solved by using tables which automatically carry formulas into new rows and are a much better way of working in general especially when named correctly.

 

[Cantrecallmyusername]

Testing out a couple of solutions here and noticed that the following works

Sub Fill_Formulas()

Dim LR As Long

LR = ActiveSheet.UsedRange.Rows.Count - Range("B1")
Range("B6:JN6").AutoFill Destination:=Range("B6:JN" & LR)
   
End Sub

Range B1 is where I am going to record the number of rows I dont want formulas to be applied so in a roundabout way the code is perfomring as I require.

I believe your problem would be solved by using tables which automatically carry formulas into new rows and are a much better way of working in general especially when named correctly.

Thanks for the comment however tables are not option with this data.