Macro to dynamically add a variable number of rows above a specific cell value

[Cantrecallmyusername]

Hi there,

I have developed a process where I need to add new names to a sheet from time to time - there is a formula on a tab which will give the number of new rows required

This is in Cell E2 on my Workings Tab
This value can also be zero which will result in no new rows needed.

When the value is greater than 0 I will need a macro to add that number of rows above the text "Uplift" in column A on my Detail tab

Using the record macro function is fine though I need to be able to dynamically add the number of rows based on the value in E2 which is causing me issues.
Help very much appreciated.

 

[Legacy 143009]

Sub test()
  Dim lRow As Long
  lRow = Cells(Rows.Count, 5).End(xlUp).Row
  For i = lRow to 2 Step - 1
    For j = 1 To Cells(i, 5).Value
      Rows(i).EntireRow.Insert
    Next
  Next
End Sub

 

[mohadin]

Hi
what about

Sub test()
Sheets("Detail ").Range(Cells.Find("Uplift", , , 1).Address).Resize(Sheets("sheet2").Range("E2").Value).EntireRow.Insert
End Sub

 

[Cantrecallmyusername]

Hi
what about

Sub test()
Sheets("Detail ").Range(Cells.Find("Uplift", , , 1).Address).Resize(Sheets("sheet2").Range("E2").Value).EntireRow.Insert
End Sub

Hi,
thanks for the reply - I ran this code and I am getting subscript out of range.
I did make a change after this error - I updated the Sheet2 reference to "Workings" as this is where the reference is but to no avail - both versions returned the same error message.

 

[Cantrecallmyusername]

Sub test()
  Dim lRow As Long
  lRow = Cells(Rows.Count, 5).End(xlUp).Row
  For i = lRow to 2 Step - 1
    For j = 1 To Cells(i, 5).Value
      Rows(i).EntireRow.Insert
    Next
  Next
End Sub

Hi,
Thanks for the reply - this works kinda
It is dynamically adding rows based on the variable number of rows required however it is adding the rows on the Workings tab and from the first row where there is data.
I need this to insert this number of rows in the detail tab above the reference "Uplift" which is in column A.

 

[Legacy 143009]

Oh, now I understand. Ok, in regard of @mohadin 's code:

Sub test()
Worksheets("Detail").Range(Cells.Find("Uplift", , , 1).Address).Resize(Worksheets("Workings").Range("E2").Value).EntireRow.Insert
End Sub

 

[Legacy 143009]

I think the issue is you can't use the address property of a found item directly. First set to a range:

Sub test()
  Set rng = Worksheets("Detail").Cells.Find("Uplift", , , 1)
  Worksheets("Detail").Range(rng.Address).Resize(Worksheets("Workings").Range("E2").Value).EntireRow.Insert
End Sub

 

[Cantrecallmyusername]

I think the issue is you can't use the address property of a found item directly. First set to a range:

Sub test()
  Set rng = Worksheets("Detail").Cells.Find("Uplift", , , 1)
  Worksheets("Detail").Range(rng.Address).Resize(Worksheets("Workings").Range("E2").Value).EntireRow.Insert
End Sub

This has worked perfectly - though one issue will be if the number of rows to add on occasion is Zero - I think the it will not like this?

 

[Legacy 143009]

How about this one?

Sub test()
  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

 

[Cantrecallmyusername]

Sub test() 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

That has met the ask - thank you for this.