Transferring data by row onto new tab Unpaid and Paid Sales

[Closed]
Report
Posts
1
Registration date
Saturday July 9, 2016
Status
Member
Last seen
July 9, 2016
-
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
-
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 :-)

1 reply

Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
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