Transferring data by row onto new tab Unpaid and Paid Sales Closed

Question

Hi I am new to this forum.

I have an excel spreadsheet which looks like this:

Date    Description    Invoice    Destination    Amount   Status

I have two tabs one names UNPAID and the other named PAID

On tab one I list all my unpaid invoices, once they have been paid I would like to type paid into the status column and I would like the data to transfer across to sheet 2 (PAID).

I can only use the very basic formulae on excel so please explain in a very basic way so that I can follow the information step by step - Apologies for sounding a little thick - I have looked at a couple of similar questions and I do not have a clue about how or where you would add all the code that I am seeing?  Any help would be massively appreciated otherwise I will have to revert back to my old style cut and paste which is a little time consuming!  Thanks again :-)

vcoolio · Answer

Hello Miliminx,

The following code should do the task for you:-


Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Intersect(Target, Columns("F:F")) Is Nothing Then Exit Sub

If Target.Value = "Paid" Then
        Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        'Target.EntireRow.Delete
        End If
  
Sheet2.Columns.AutoFit

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

When the word "Paid" is typed into any cell in Column F (Status Column) and you click away or press enter or down arrow, the relevant row of data is transferred to sheet2 (Paid).

To implement the code, right click on the "Unpaid" sheet tab and from the menu that appears, select 'view code". In the big white field that then appears, paste the above code.
Go back to the "Unpaid" sheet and type "Paid" in any cell in Column F then click away (or press enter or down arrow) and you will see that the data has been transferred to sheet2 ("Paid"). 

If you wish to delete the "used" data from the "Unpaid" sheet once a row of data has been transferred, then in the above code you will see that line 11 is in green font with an apostrophe in front of it. Remove the apostrophe and the delete line of code will be activated.

Following is the link to my test work book for you to play with:-

https://www.dropbox.com/s/hsk7srqrn2wsf4r/Miliminx%28worksheet_change%20event%29.xlsm?dl=0

Type "Paid" into any cell in Column F to see it work.

I hope that this helps.

Cheerio,
vcooliol

Transferring data by row onto new tab Unpaid and Paid Sales

1 response

Similar discussions

Subscribe To Our Newsletter!