vba to insert copied row below row of a specific value

[DRWonoski]

I'm new to VBA but trying to learn and wrap my head around the way vba works. What I need can be seen below:

Formula1	Formula2	Formula3	Formula4	1234	Step1	Step2	Step3	DOG	Complete
Formula1	Formula2	Formula3	Formula4	5678	Step1	Step2		DOG
Formula1	Formula2	Formula3	Formula4	4321	Step1	Step2		DOG
Formula1	Formula2	Formula3	Formula4	8765	Step1			DOG
Formula1	Formula2	Formula3	Formula4	2468	Step1	Step2	Step3	CAT	Complete
Formula1	Formula2	Formula3	Formula4	1359	Step1	Step2		CAT
Formula1	Formula2	Formula3	Formula4	8642	Step1			CAT

<tbody>
</tbody>

This table represents a status of open orders in a spreadsheet. I'd like to create a button that when clicked, finds the last occurrence of "Cat" or "Dog" (which I will tell it), copy the last row of that occurrence, and paste it as a new row below the last. Note: the blank row inbetween DOG and CAT is important and must stay. I'd also like it to copy the formulas from Cells 1-4.

How best would you do set up the vba? My thought is to do these steps in this order:
1) inputBox pops up for the user to enter the PO number (1234, 4321 found above).
2) find last occurrence of Dog
3) Copy row of last occurrence of dog
4) Insert Copied cells from step above
5) if "Step2, Step3 or "Complete" are filled in, make those cells blank.

These are the steps I would take if I were manually adding a new row but I feel like VBA may have some ways to accomplish it without all of those steps needed.

 

[RCBricker]

I'm new to VBA but trying to learn and wrap my head around the way vba works. What I need can be seen below:

Formula1	Formula2	Formula3	Formula4	1234	Step1	Step2	Step3	DOG	Complete
Formula1	Formula2	Formula3	Formula4	5678	Step1	Step2		DOG
Formula1	Formula2	Formula3	Formula4	4321	Step1	Step2		DOG
Formula1	Formula2	Formula3	Formula4	8765	Step1			DOG
Formula1	Formula2	Formula3	Formula4	2468	Step1	Step2	Step3	CAT	Complete
Formula1	Formula2	Formula3	Formula4	1359	Step1	Step2		CAT
Formula1	Formula2	Formula3	Formula4	8642	Step1			CAT

<tbody>
</tbody>

This table represents a status of open orders in a spreadsheet. I'd like to create a button that when clicked, finds the last occurrence of "Cat" or "Dog" (which I will tell it), copy the last row of that occurrence, and paste it as a new row below the last. Note: the blank row inbetween DOG and CAT is important and must stay. I'd also like it to copy the formulas from Cells 1-4.

How best would you do set up the vba? My thought is to do these steps in this order:
1) inputBox pops up for the user to enter the PO number (1234, 4321 found above).
2) find last occurrence of Dog
3) Copy row of last occurrence of dog
4) Insert Copied cells from step above
5) if "Step2, Step3 or "Complete" are filled in, make those cells blank.

These are the steps I would take if I were manually adding a new row but I feel like VBA may have some ways to accomplish it without all of those steps needed.

try this

Sub DRWonoski()

Dim wb As Workbook
Dim ws As Worksheet
Dim lngrow As Long, lngcol As Long
Dim rng As Range, rngFIND As Range, rngCOPY As Range
Dim strRNG As Variant
Dim intPO As Integer, intITEM As Integer, intST2 As Integer, intST3 As Integer, intSTA As Integer, intROW As Integer
Dim i As Variant, j As Variant

    Set wb = ThisWorkbook
    Set ws = ActiveSheet
    Set rng = Application.InputBox(Prompt:="Please select the PO column header cell.", Type:=8)
    intPO = rng.Column
    intST2 = intPO + 2
    intST3 = intPO + 3
    intITEM = intPO + 4
    intSTA = intPO + 5
    strRNG = InputBox("Please enter the PO number.", "PO Number")
    With ws
        lngrow = ws.Cells(ws.Rows.Count, rng.Column).End(xlUp).Row
        lngcol = ws.Cells(rng.Row, ws.Columns.Count).End(xlToLeft).Column
        Set rngFIND = ws.Range(ws.Cells(rng.Row, rng.Column), ws.Cells(lngrow, rng.Column))
        intROW = rngFIND.Find(strRNG).Row
        strRNG = ws.Cells(intROW, intITEM).Value
        
        For i = lngrow To rng.Row Step -1
            If CStr(ws.Cells(i, intITEM).Value) = strRNG Then
                j = i + 1
                Set rngCOPY = ws.Cells(i, intITEM).EntireRow
                rngCOPY.EntireRow.Copy
                ws.Rows(i & ":" & i).Insert shift:=xlDown
                ws.Cells(j, intST2).Value = ""
                ws.Cells(j, intST3).Value = ""
                ws.Cells(j, intSTA).Value = ""
                Exit For
            End If
        Next
    End With
End Sub