Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Transferring data into a new worksheet when it meets a criteria [Closed]

Report
Posts
1
Registration date
Tuesday June 13, 2017
Status
Member
Last seen
June 13, 2017
 -
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 -
I have a worksheet where we track whether a job is late or not. If it is late, one of the cells is "TRUE." I want all the "TRUE" rows to transfer into a new worksheet, with all of the data from that row transferred over. How can I do this?
See more

1 reply

Answer 1 / 1
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 213
Hello Dcree,

Try the following code, placed in the sheet1 module:- 

Private Sub Worksheet_Change(ByVal Target As Range)

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

Application.ScreenUpdating = False

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

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


The code assumes that the criteria "True" is in Column D of Sheet1(change the Column letter to suit your needs). Each row with the "True" criteria in Column D will be transferred to Sheet 2. The "used" row of data in Sheet 1 will be cleared.

To implement the code:-

- Right click on the Sheet1 tab.
- Select "view code" from the menu that appears.
- In the big white field that then appears, paste the above code.

Every time that you place "True" in any cell in Column D and then click away (or press enter or down arrow) the entire row of relevant data will be transferred to Sheet2. Make sure that "True" is the last entry in a row.

Test the code in a copy of your work book first.

I hope that this helps.

Cheerio,
vcoolio.
Ask a question