Previous Topic Next Topic

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
1344
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 17, 2022
 - Apr 7, 2017 at 08:24 AM
Hello,

In a file, I am trying to copy data from one row of a worksheet if specific criteria are met in one column to another worksheet.

Ex: If in Sheet 1, Column F there is a "1", I want that entire row in Sheet 1 to copy over to Sheet 2.

Thanks

1 reply

Answer 1 / 1
vcoolio
Posts
1344
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 17, 2022
 249
Apr 7, 2017 at 08:24 AM
Hello Schme73,

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.
0