Pasting a Row Based on Column Criteria.

[Throwaway]

Hello all,
I have a worksheet, where in Column F, there are about 200 instances of a cell containing the text "EBITDA". I have used a macro to insert an extra row below each of these instances, however I'm looking to copy and past the contents (formulas & Format) of Row 31 so that it can be pasted each time directly below any row where column F has "EBITDA" in it.

The rows in between each EBITDA are irregular, so using a filter to paste the formula does not seem viable. Any macro or work-around would be appreciated.

Thanks in advance!

 

[Truiz]

If I understood you correctly this should do it:

Sub Kakaroto()

Dim lrow As Long
Dim iCntr As Long


lrow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row


For iCntr = lrow To 1 Step -1
    If Cells(iCntr, "F") = "EBITDA" Then
    Rows(iCntr + 1) = Rows(31)
    End If
Next
End Sub

 

[Truiz]

An alternative

Sub Kakaroto()
Dim lrow As Long
Dim iCntr As Long
Dim i As String


lrow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row

i = "EBITDA"

For iCntr = lrow To 1 Step -1
    Select Case Cells(iCntr, "F")
    Case i
    Rows(31).Copy Rows(iCntr)
    End Select
Next
End Sub

 

[Throwaway]

Truiz,
Thanks for responding! It looks like your second macro does indeed copy/past all formulas in row 31 as desired, however, it looks like the copied row is pasting in the same row anytime "EBITDA" is present in column F, whereas I'm looking for it to paste in the row below any cell "EBITDA" is present.

Thanks again for your help!

 

[Truiz]

Try:

[COLOR=#333333]Sub Kakaroto()[/COLOR]Dim lrow As Long
Dim iCntr As Long
Dim i As String


lrow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row

i = "EBITDA"

For iCntr = lrow To 1 Step -1
    Select Case Cells(iCntr, "F")
    Case i
    Rows(31).Copy Rows(iCntr + 1)
    End Select
Next [COLOR=#333333]End Sub[/COLOR]