Results 1 to 12 of 12
  1. #1
    Join Date
    May 2013
    Posts
    5

    Unanswered: [SOLVED] Copying and deleteing rows based on a cell value with VBA

    Hi there. I'm relatively new to VBA coding. So far I have managed to wrangle together, by searching the interenet, a simple VBA code to copy, paste and then delete rows of data onto a second worksheet, based on a value put into a certain cell.

    The Situation:
    My database has 3 worksheets: Owing, Paid and Withdrawn. 'Owing' being the initial sheet. From this, I want to be able to select 'Paid' or 'Withdrawn' from an in-cell drop down list in column 'I', and then have that row taken from the 'Owing' worksheet to the relevent worksheet ('Paid' or 'Withdrawn').

    The Code:
    The code I have written works well. It takes the data from 'Owing' and places it in 'Paid' of 'Withdrawn' as requested, and even deletes the row from 'Owing'.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
        If Target = "Paid" Then
            Target.EntireRow.Copy Sheets("Paid").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            Target.EntireRow.Delete
        ElseIf Target = "Withdrawn" Then
            Target.EntireRow.Copy Sheets("Withdrawn").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            Target.EntireRow.Delete
        End If
    End Sub
    After that, it brings up run-time error 13. Debugging highlights the line:
    Code:
        If Target = "Paid" Then
    My Plea:
    I would be very grateful if someone with the know-how could assist in my (albeit feeble) attempts at VBA coding.
    Last edited by Excel_N00b; 05-12-13 at 20:44.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You've put the code in the worksheet's Change event. That means that it fires every time a range in the worksheet is changed.

    The line
    Code:
    Target.EntireRow.Delete
    causes the routine to fire again. However, this time around, Target points to a row that has been deleted. Because it's not there, you can't use it in an If statement!

    Try replacing it with
    Code:
    Target.ClearContents
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    May 2013
    Posts
    5
    Thanks for your help, weejas.

    Unfortunately, deleteing the data in the target cell wasn't what I wanted the code to do.

    I have taken out the 'delete row' command, as follows, is there anyway code can be included that will delete the row that I have just copied - preferrably at the same time as it is copied (maybe a 'cut and paste' then delete the empty row in the original spreadsheet?) without the need of including a button?

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
        If Target = "Paid" Then
            Target.EntireRow.Copy Sheets("Paid").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        ElseIf Target = "Withdrawn" Then
            Target.EntireRow.Copy Sheets("Withdrawn").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
       End Sub
    Thanks in advance!
    Last edited by Excel_N00b; 05-10-13 at 00:01.

  4. #4
    Join Date
    May 2013
    Posts
    5
    If it helps any, I've attached the spreadsheet with the simplified code.
    Attached Files Attached Files

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Right, with both attempts, you were most of the way there! I liked the escape clause at the beginning, too.

    I've simplified your code a bit - it now depends on the names of the values in column "Outcome" being the same as the destination sheets. If this ever changes, you can simply take the value from the target range into a variable and run it through a translation SELECT CASE statement.

    Have a look and see if this does what you want.
    Attached Files Attached Files
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Feb 2013
    Posts
    15
    Hello Friend....

    Really thanks for sharing useful and very informative information about this post.

  7. #7
    Join Date
    May 2013
    Posts
    5
    Quote Originally Posted by weejas View Post
    Right, with both attempts, you were most of the way there! I liked the escape clause at the beginning, too.

    I've simplified your code a bit - it now depends on the names of the values in column "Outcome" being the same as the destination sheets. If this ever changes, you can simply take the value from the target range into a variable and run it through a translation SELECT CASE statement.

    Have a look and see if this does what you want.
    You are a legend! It works perfectly (and I understand the code, so that's a plus!). You deserve a very big internet hug!! Thank you!

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Happy to help!

    It's how I got started - working out partial solutions, and getting help on this forum
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    Jul 2013
    Posts
    8
    I am basically trying to do the same thing as well but struggling with the code as its been a very very long time since i used vba. I am creating a project tracker where the user basically has 2 options from a drop down box either "In Progress" or "Done". When they select "Done" i can use this code to get it to copy and delete the row to a Sheet called "Done" (which i would like to say Completed Projects, but not sure how to assign variables yet). When they select "In Progress" i want nothing to happen. I assume i need to run some type of Elseif checker to end the sub if "Done" is not selected but again i know nothing on this stuff.

    Another little addition id like to make would be in the case of an error by the user, say accidentally selecting wrong project as Done, having the option to go to completed projects and moving back to sheet "Active Projects". Manual cut/paste messes up the code.

    Don't mean to thread jack, didn't want to start a whole new topic when this is almost exactly what i am trying to do just with little tweaks.

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Post what you've got and I'll have a look when time allows
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  11. #11
    Join Date
    Jul 2013
    Posts
    8
    Quote Originally Posted by weejas View Post
    Post what you've got and I'll have a look when time allows
    Thank you I appreciate the help. I ended up going a different route with the code then the original poster and i actually got it to copy to the new sheet and delete, but id still like the ability to move it back to active sheets should there be a mistake in selecting "Done" on a project.

    My Active Sheet has a "Date Recieved" column, any chance to change this to the current date when its copied over to "Completed Projects".
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim wsUse As Worksheet
        Dim wsDc As Worksheet
        Dim strdc As String
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        If Target.Cells.Count > 1 Then Exit Sub
         
        If Target.Column <> 4 Then Exit Sub
        Set wsUse = ThisWorkbook.Sheets("Active Projects")
        Set wsDc = ThisWorkbook.Sheets("Completed Projects")
         
         
        With wsUse
            strdc = Target.Value
             
            If strdc = "Done" Then
                n = .Rows.Count
                Target.EntireRow.Copy
                wsDc.Range("A" & n).End(xlUp).Offset(1, 0).PasteSpecial xlValues
                wsDc.Range("A" & n).End(xlUp).EntireRow.PasteSpecial xlPasteFormats
                Application.CutCopyMode = False
                Target.EntireRow.Delete
            End If
             
        End With
         
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
    End Sub
    Thanks again.

  12. #12
    Join Date
    Jul 2013
    Posts
    8
    oh and still cant get it to copy a certain range in the row. Don't want the last column copied over

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •