Copy rows from one tab to another - if specific criteria in col
Closed
schme73
Posts
1
Registration date
Thursday April 6, 2017
Status
Member
Last seen
April 6, 2017
-
Apr 6, 2017 at 01:17 PM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - Apr 7, 2017 at 08:24 AM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - Apr 7, 2017 at 08:24 AM
Related:
- Copy rows from one tab to another - if specific criteria in col
- If cell contains (multiple text criteria) then return (corresponding text criteria) ✓ - Excel Forum
- Excel conditional formatting if another cell contains specific text ✓ - Excel Forum
- Macro to copy data from one sheet to another based on criteria ✓ - Excel Forum
- Transfer data from one excel worksheet to another automatically based on criteria - Guide
- If cell contains specific text then return value in another cell vba ✓ - Excel Forum
1 reply
vcoolio
Posts
1362
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2022
252
Apr 7, 2017 at 08:24 AM
Apr 7, 2017 at 08:24 AM
Hello Schme73,
Try the following code, placed in a standard module and assigned to a button:-
The code filters Sheet1 Column F for the number 1 and then transfers the relevant rows of data to Sheet 2. Did you want to clear the "used" data from Sheet 1 once transferred to Sheet 2? If so, remove the apostrophe from the begining of line 12 (in green font) in the code above. This will activate the delete line of code.
Test the code in a copy of your workbook first.
I hope that this helps,
Cheerio,
vcoolio.
Try the following code, placed in a standard module and assigned to a button:-
Sub DataTransfer() Application.ScreenUpdating = False With ActiveSheet .AutoFilterMode = False With Range("F1", Range("F" & Rows.Count).End(xlUp)) .AutoFilter 1, 1 If Range("F" & Rows.Count).End(xlUp).Row > 1 Then .Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2) '.Offset(1).EntireRow.Delete End If End With ActiveSheet.[F1].AutoFilter End With Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
The code filters Sheet1 Column F for the number 1 and then transfers the relevant rows of data to Sheet 2. Did you want to clear the "used" data from Sheet 1 once transferred to Sheet 2? If so, remove the apostrophe from the begining of line 12 (in green font) in the code above. This will activate the delete line of code.
Test the code in a copy of your workbook first.
I hope that this helps,
Cheerio,
vcoolio.