Transferring data into a new worksheet when it meets a criteria
Closed
dcree
Posts
1
Registration date
Tuesday June 13, 2017
Status
Member
Last seen
June 13, 2017
-
Updated on Jun 15, 2017 at 05:31 AM
vcoolio Posts 1371 Registration date Thursday July 24, 2014 Status Moderator Last seen April 12, 2023 - Jun 14, 2017 at 07:37 AM
vcoolio Posts 1371 Registration date Thursday July 24, 2014 Status Moderator Last seen April 12, 2023 - Jun 14, 2017 at 07:37 AM
Related:
- Transferring data into a new worksheet when it meets a criteria
- Transfer data from one excel worksheet to another automatically - Guide
- How do you add a username or author to a worksheet - Guide
- Digital data transmission - Guide
- Automatically run a macro when opening a worksheet - Guide
- How to be offline in whatsapp when data is on - Guide
1 reply
vcoolio
Posts
1371
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
April 12, 2023
252
Jun 14, 2017 at 07:37 AM
Jun 14, 2017 at 07:37 AM
Hello Dcree,
Try the following code, placed in the sheet1 module:-
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.
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.