So I am working on a project that is a spreadsheet that tracks orders. When the order is in house and received and the drop down shows received the code is supposed to copy that row and move it to Sheet2 and paste it into the next available line and then delete it from the first sheet (SheetA) and then move all the information up to close the gaps. I have two bits of code that I have been trying to get to work but for some reason neither will do anything like I was hoping for. Here is the first bit of code:
[vba]Sub CopyPaste()
Dim MyRow As Integer
Dim MyCell As String
Dim LookCell As String
Dim TargetRow As Integer
Dim TargetRange As String
'Find first open row on sheet 2
TargetRow = 2
Do While Sheet2.Range("A" & CStr(TargetRow)).Value <> Empty
TargetRow = TargetRow + 1
Loop
TargetRange = "A" & CStr(TargetRow) & ":L" & CStr(TargetRow)
'Search Sheet 1 for L column = "4"
MyRow = 2
MyCell = "A" & CStr(MyRow)
LookCell = "L" & CStr(MyRow)
Do While Sheet2.Range(MyCell).Value <> Empty
If SheetA.Range(LookCell).Value = "4" Then
Sheet2.Range(TargetRange).Value = SheetA.Range(MyCell & ":L" & CStr(MyRow)).Value
SheetA.Range(MyCell).EntireRow.Delete (xlShiftUp)
TargetRow = TargetRow + 1
TargetRange = "A" & CStr(TargetRow) & ":L" & CStr(TargetRow)
Else
MyRow = MyRow + 1
End If
MyCell = "A" & CStr(MyRow)
LookCell = "L" & CStr(MyRow)
Loop
End Sub
[/vba]
And here is the second bit. The theory behind the second bit is to work from the bottom of the list up but I cant seem to get it to work either since I am really not a VBA programmer and am very new at this.
[vba]For I = Sheet2.Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
MyCell = "A" & I
LookCell = "L" & I
If SheetA.Range(LookCell).Value = "4" Then
Sheet2.Range(TargetRange).Value = SheetA.Range(MyCell & ":L" & CStr(MyRow)).Value
SheetA.Range(MyCell).EntireRow.Delete (xlShiftUp)
TargetRow = TargetRow + 1
TargetRange = "A" & CStr(TargetRow) & ":L" & CStr(TargetRow)
End If
Next I[/vba]
I also have the worksheet on my drop box as well. Here is the link. Any help or suggestions would be greatly appreciated!