vba to insert copied row below row of a specific value

D

DRWonoski

Board Regular
Joined
Mar 20, 2014
Messages
99
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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Formula1[/TD]
[TD]Formula2[/TD]
[TD]Formula3[/TD]
[TD]Formula4[/TD]
[TD]1234[/TD]
[TD]Step1[/TD]
[TD]Step2[/TD]
[TD]Step3[/TD]
[TD]DOG[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]Formula1[/TD]
[TD]Formula2[/TD]
[TD]Formula3[/TD]
[TD]Formula4[/TD]
[TD]5678[/TD]
[TD]Step1[/TD]
[TD]Step2[/TD]
[TD][/TD]
[TD]DOG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula1[/TD]
[TD]Formula2[/TD]
[TD]Formula3[/TD]
[TD]Formula4[/TD]
[TD]4321[/TD]
[TD]Step1[/TD]
[TD]Step2[/TD]
[TD][/TD]
[TD]DOG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula1[/TD]
[TD]Formula2[/TD]
[TD]Formula3[/TD]
[TD]Formula4[/TD]
[TD]8765[/TD]
[TD]Step1[/TD]
[TD][/TD]
[TD][/TD]
[TD]DOG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula1[/TD]
[TD]Formula2[/TD]
[TD]Formula3[/TD]
[TD]Formula4[/TD]
[TD]2468[/TD]
[TD]Step1[/TD]
[TD]Step2[/TD]
[TD]Step3[/TD]
[TD]CAT[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]Formula1[/TD]
[TD]Formula2[/TD]
[TD]Formula3[/TD]
[TD]Formula4[/TD]
[TD]1359[/TD]
[TD]Step1[/TD]
[TD]Step2[/TD]
[TD][/TD]
[TD]CAT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula1[/TD]
[TD]Formula2[/TD]
[TD]Formula3[/TD]
[TD]Formula4[/TD]
[TD]8642[/TD]
[TD]Step1[/TD]
[TD][/TD]
[TD][/TD]
[TD]CAT[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
DRWonoski said:
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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Formula1[/TD]
[TD]Formula2[/TD]
[TD]Formula3[/TD]
[TD]Formula4[/TD]
[TD]1234[/TD]
[TD]Step1[/TD]
[TD]Step2[/TD]
[TD]Step3[/TD]
[TD]DOG[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]Formula1[/TD]
[TD]Formula2[/TD]
[TD]Formula3[/TD]
[TD]Formula4[/TD]
[TD]5678[/TD]
[TD]Step1[/TD]
[TD]Step2[/TD]
[TD][/TD]
[TD]DOG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula1[/TD]
[TD]Formula2[/TD]
[TD]Formula3[/TD]
[TD]Formula4[/TD]
[TD]4321[/TD]
[TD]Step1[/TD]
[TD]Step2[/TD]
[TD][/TD]
[TD]DOG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula1[/TD]
[TD]Formula2[/TD]
[TD]Formula3[/TD]
[TD]Formula4[/TD]
[TD]8765[/TD]
[TD]Step1[/TD]
[TD][/TD]
[TD][/TD]
[TD]DOG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula1[/TD]
[TD]Formula2[/TD]
[TD]Formula3[/TD]
[TD]Formula4[/TD]
[TD]2468[/TD]
[TD]Step1[/TD]
[TD]Step2[/TD]
[TD]Step3[/TD]
[TD]CAT[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]Formula1[/TD]
[TD]Formula2[/TD]
[TD]Formula3[/TD]
[TD]Formula4[/TD]
[TD]1359[/TD]
[TD]Step1[/TD]
[TD]Step2[/TD]
[TD][/TD]
[TD]CAT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula1[/TD]
[TD]Formula2[/TD]
[TD]Formula3[/TD]
[TD]Formula4[/TD]
[TD]8642[/TD]
[TD]Step1[/TD]
[TD][/TD]
[TD][/TD]
[TD]CAT[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.
Click to expand...


try this

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

4
  • Question Question
VBA - Dynamic Cell Shifting
Replies
4
Views
2K
4EverStudent
4
M
  • Solved
cut/copy and paste row based on cell value not being equal to specific text
Replies
6
Views
494
mlan1604
M
Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
272
sijpie
sijpie
C
  • Question Question
Inserting rows when value's are input into cells
Replies
0
Views
827
cdude911
C
K
  • Question Question
VBA for using a check box to Regen that row and then uncheck the box (ready to run macro again)
Replies
1
Views
683
Micron
M

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top