Copy row to another sheet and delete (based on cell content)

**mpower87** · 06-29-2010, 12:19 AM

Hi guys, I would like to find an alternative to my code which doesnt work properly at the moment.

Private Sub CleanUpList()
Dim CompletedOrderRow As Long, j

j = 2
'loop - check if R is entered in list until it reaches an empty row
Do Until Range("L" & j) = ""
If Range("L" & j) = "D" Then
'move order to Completed Orders list
CompletedOrderRow = Sheets("Completed Orders").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Completed Orders").Cells(CompletedOrderRow, 1).Value = Range("A" & j).Value
Sheets("Completed Orders").Cells(CompletedOrderRow, 2).Value = Range("B" & j).Value
Sheets("Completed Orders").Cells(CompletedOrderRow, 3).Value = Range("C" & j).Value
Sheets("Completed Orders").Cells(CompletedOrderRow, 4).Value = Range("D" & j).Value
Sheets("Completed Orders").Cells(CompletedOrderRow, 5).Value = Range("E" & j).Value
Sheets("Completed Orders").Cells(CompletedOrderRow, 6).Value = Range("F" & j).Value
Sheets("Completed Orders").Cells(CompletedOrderRow, 7).Value = Range("G" & j).Value
Sheets("Completed Orders").Cells(CompletedOrderRow, 8).Value = Range("H" & j).Value
Sheets("Completed Orders").Cells(CompletedOrderRow, 9).Value = Range("I" & j).Value
Sheets("Completed Orders").Cells(CompletedOrderRow, 10).Value = Range("J" & j).Value
Sheets("Completed Orders").Cells(CompletedOrderRow, 11).Value = Range("K" & j).Value
Sheets("Completed Orders").Cells(CompletedOrderRow, 12).Value = Range("L" & j).Value
Sheets("Completed Orders").Cells(CompletedOrderRow, 13).Value = Range("M" & j).Value
Sheets("Completed Orders").Cells(CompletedOrderRow, 14).Value = Range("N" & j).Value
'delete the row
Cells(j, 1).EntireRow.Delete
'next row
j = j + 1
'if no R try next row
Else
j = j + 1

End If
Loop

End Sub

I need an efficient code which will copy all the rows with "D" in cell in column L to another sheet (CompeletedOrders) and then delete the rows in Orders sheet.

Please assist with this, Cheers.

**pike** · 06-29-2010, 07:38 AM

hi mpower87
at a glance

Sheets("Completed Orders").Cells(CompletedOrderRow, 1).resie(1,14).copy  Range("A" & j).Value
Sheets("Completed Orders").Cells(CompletedOrderRow, 1).resie(1,14).clearcontents

or find syntax mabe be a better option

**mpower87** · 06-29-2010, 10:25 PM

hi pike,

thanks for your reply. can you please explain what you mean there? and i tried that code but it doesnt work...

**pike** · 06-30-2010, 04:13 AM

Hi mpower87,
try..

Do Until Range("L" & j) = ""
If Range("L" & j) = "D" Then
'move order to Completed Orders list
CompletedOrderRow = Sheets("Completed Orders").Range("A" & Rows.Count).End(xlUp).Row + 1

Sheets("Completed Orders").Cells(CompletedOrderRow, 1).resize(1,14).copy  destination:=Range("A" & j).Value
Cells(j, 1).EntireRow.Delete

**mpower87** · 07-01-2010, 01:56 AM

hi mate, thanks for that, i tried it as:

Private Sub CleanUpList1()
Dim CompletedOrderRow As Long, j
j = 2
Do Until Range("L" & j) = ""
If Range("L" & j) = "D" Then
'move order to Completed Orders list
CompletedOrderRow = Sheets("Completed Orders").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Completed Orders").Cells(CompletedOrderRow, 1).Resize(1, 14).Copy Destination:=Range("A" & j).Value
Cells(j, 1).EntireRow.Delete
j = j + 1
Else
j = j + 1
End If
Loop
End Sub

Unfortunately this resulted in an error:

Run-time error '1004':

Copy method of Range class failed

**mpower87** · 07-02-2010, 12:22 AM

no one else can have a crack at this?

**pike** · 07-02-2010, 03:03 AM

dude can you attach a sample workbook to play with

**pike** · 07-02-2010, 03:05 AM

change this bit

Sheets("Completed Orders").Cells(CompletedOrderRow, 1).Resize(1, 14).Copy Destination:=Range("A" & j)

**pike** · 07-02-2010, 03:18 AM

or

Range("A" & j).Resize(1, 14).Copy Destination:Sheets("Completed Orders").Cells(CompletedOrderRow, 1)

**snb** · 07-02-2010, 04:26 AM

Private Sub CleanUpList1()
  with sheets(1).Usedrange
      .columns(12).autofilter ,1 "D"
      .offset(1).specialcells(xlcelltypevisible).copy 
Sheets("Completed Orders").Cells(Rows.Count,1).End(xlUp).offset(1)
      .offset(1).specialcells(xlcelltypevisible).entirerow.delete
      .autofilter
  End With
End Sub

**mpower87** · 07-04-2010, 07:56 PM

hi guys, thanks for you for your replies. unfortunately i can't get your code to work

attached is a dummy file that may assist you with helping me. i appreciate your time. cheers.

**pike** · 07-05-2010, 02:55 AM

mpower87, matie, mate, maaaaate

You FORGOT to mention the worksheet change event!!!!!!

Private Sub CleanUpList()
Dim CompletedOrderRow As Long, iRow
For iRow = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
If Range("L" & iRow) = "D" Then
CompletedOrderRow = Sheets("Completed Orders").Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & iRow).Resize(1, 14).Copy Destination:=Sheets("Completed Orders").Cells(CompletedOrderRow, 1)
Cells(iRow, 1).EntireRow.Delete
End If
Next
End Sub

**mpower87** · 07-05-2010, 06:43 PM

hah, well I'm still a noob when it comes to vba. thanks for pointing that out and making a working piece of code pike. much appreciated. kudos to you mate

**pike** · 07-05-2010, 07:42 PM

Hey mpower87

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Copy row to another sheet and delete (based on cell content)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy row to another sheet and delete (based on cell content)

Re: Copy row to another sheet and delete (based on cell content)

Re: Copy row to another sheet and delete (based on cell content)

Re: Copy row to another sheet and delete (based on cell content)

Re: Copy row to another sheet and delete (based on cell content)

Re: Copy row to another sheet and delete (based on cell content)

Re: Copy row to another sheet and delete (based on cell content)

Re: Copy row to another sheet and delete (based on cell content)

Re: Copy row to another sheet and delete (based on cell content)

Re: Copy row to another sheet and delete (based on cell content)

Re: Copy row to another sheet and delete (based on cell content)

Re: Copy row to another sheet and delete (based on cell content)

Re: Copy row to another sheet and delete (based on cell content)

Re: Copy row to another sheet and delete (based on cell content)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions