Automatically transfer excel row to a different sheet based Closed

Question

Hello, 

     I have a spreadsheet for paid & unpaid invoices from our customers and would like to have the row automatically move once I mark it paid or mark how it was paid. Any help on this would be greatly appreciated. If I need to email my worksheet I can do that as well. Thank you in advance 

Rachael

Configuration: Windows / Mozilla 11.0

vcoolio · Answer

Hello Rachael,

Perhaps the following code will do the job for you:-

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

If Target.Count > 1 Then Exit Sub    ' this stops code error if more than one cell is changed at once
If Not Application.Intersect(Target, Range("E:E")) Is Nothing Then

If Target = "Paid" Then
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        Target.EntireRow.Delete
        End If
  End If
  
Sheet2.Columns.AutoFit
Sheet2.Select

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

It is a worksheet_change event type of code which means that every time you type in "Paid" in (in this case) Column E and click away, the entire row of data associated with the "Paid" status will be transferred to Sheet2. The relevant row of data is then deleted leaving you with only the unpaid invoices to deal with.

I'm only guessing as to what your actual work book looks like but following is a link to my test work book which will give you an idea of how it all works. In the code, you'll have to change the cell references to suit yourself:-

https://www.dropbox.com/s/bpe4rbcetaz7cva/Rachael.xlsm?dl=0

To implement the code (try it in copy of your work book first), right click on your main sheet tab, select "view code" from the menu and in the big white field that then appears, paste the above code.

Go back to your main work sheet, type in "Paid" somewhere in Column E then click away (or press down arrow or enter) to execute the code.

I hope that this helps.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Rachael,

Do the DropBox thing and post back with the link to your file (just like I did above). This way, the whole solution stays public (as this is a public forum).

Be careful with any sensitive data.

Cheerio,
vcoolio.

vcoolio · Answer

Hi Rachael,

For the first link that you supplied, following is the modified code. Place it in the "Outstanding" sheet module as explained in my first reply:-

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

If Target.Count > 1 Then Exit Sub    ' This stops code error if more than one cell is changed at once.
If Not Application.Intersect(Target, Range("H:H")) Is Nothing Then  'Indicates the Target range.

If LCase(Target.Value) = LCase("Paid") Then
            Range(Cells(Target.Row, "B"), Cells(Target.Row, "I")).Copy Sheet3.Range("B" & Rows.Count).End(3)(2)
            Target.EntireRow.Delete
      End If
End If
    
Sheet3.Columns.AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Following is the link to the sample with the code implemented:-

https://www.dropbox.com/s/jf6xun76mce4c30/Rachael%282%29.xlsm?dl=0

You'll see that the target range (Status) is H:H. The copy range is from Column B to Column I. 
You'll also note that I've referred to the destination sheet by its sheet code (Sheet3) rather than its sheet name. This is a more robust method. So, even if you need to change the sheet name, the code will still work regardless. You will find the sheet codes in the VBA Editor Project Explorer to the left of the main code field. As you can see, the "Outstanding" sheet code is Sheet1.

I've made the criteria "Paid" case insensitive.

Make sure that the criteria "Paid" is the last entry that you make on each row.

"Also when I was trying it on the link you sent me earlier every time I entered paid it automatically took me to the next sheet. It didn't matter if I hit enter, tab or even the arrow. I want to ask, is there a way to avoid that from happening? "
I've removed the line of code that takes you to the second sheet.

As for the second link that you supplied, I'll leave that to you as a homework exercise now that you have an idea of how the code above works. There are some notes (in green print) in the above code which do a little more explaining.

There isn't a Status column in the second sample link. So, if you create one, remember to change the target range to suit.

I hope that this helps.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Rachael,

You're welcome. Glad that I could help.

BTW, did you do your homework?

Cheerio,
vcoolio.

Automatically transfer excel row to a different sheet based

4 responses

Subscribe To Our Newsletter!