CODEWITHSUNDEEP
vbaexcel
Victoria
Victoria

Reputation: 25

Copy entire row to another excel worksheet, then delete only part of the row

So my main worksheet has a bunch of columns and the last column contains a formula that displays a status of either "In Process", "on hold", "order placed", "at company", "shipped", or "cancelled" depending on certain conditions. I'm trying to find a way to add a button that copies the cancelled to a sheet called cancelled and a shipped to a sheet called shipped orders.

My problem is that I need to copy the row from A through V, paste only the values in the next blank row in my cancelled work sheet, and then go back to the main sheet and clear the contents in cells B through R because the other cells in that row have formulas that have to stay put and once B through R is cleared out the status changes.

Can anyone help me out with this? This is all I have so far, it copies the cancelled rows but I don't know what to do after that to get it to delete the right parts.

Private Sub AutoMove_Click()
Set Rng = Range("V6:V201")
again:
For Each r In Rng
If r.Value = "Cancelled" Then
    r.EntireRow.Copy
    Worksheets("Cancelled").Activate
    erow = Worksheets("Cancelled").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Cells(erow, 1).Activate
    Selection.PasteSpecial xlPasteValues
    Worksheets("Current LVA").Activate
    End If
Next
End Sub

Upvotes: 1

Views: 2177

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

Give this a shot. Note how I declare and assigned all my objects (except for Rows.Count because it's not important in this case).

Private Sub AutoMove_Click()

    Dim wsCurr As Worksheet, wsPaste As Worksheet

    Set wsCurr = Worksheets("Current LVA")

    Dim r As Range, Rng As Range
    Set Rng = wsCurr.Range("V6:V201")

    For Each r In Rng

        Select Case r.Value

            Case Is = "Cancelled", "Shipped"

                r.EntireRow.Copy
                Set wsPaste = Worksheets(r.Value)
                wsPaste.Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                'this line will clear contents of B:R for the row in the Current sheet
                wsCurr.Range(ws.Cells(r.Row, 2), ws.Cells(r.Row, 18)).ClearContents

        End Select

    Next

End Sub

Upvotes: 1

Related Questions