Copy rows from one tab to another - if specific criteria in col
Closed
schme73
vcoolio
- Posts
- 1
- Registration date
- Thursday April 6, 2017
- Status
- Member
- Last seen
- April 6, 2017
vcoolio
- Posts
- 1344
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- May 17, 2022
Related:
- Copy rows from one tab to another - if specific criteria in col
- Code to move entire row to another tab based on cell value using google sheets ✓ - Forum - Excel
- Excel copy rows from one sheet to another based on criteria ✓ - Forum - Excel
- How to automatically copy rows in excel to another sheet based on criteria - Guide
- Copy a row from one worksheet to another, based on criteria ✓ - Forum - Excel
- Copy rows based on a condition ✓ - Forum - Excel
1 reply
vcoolio
Apr 7, 2017 at 08:24 AM
- Posts
- 1344
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- May 17, 2022
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.