Copy and paste rows based on multiple criteria

  • I want to copy rows from sheet "TrackA" to sheet "RegionQualifiers" based on certain criteria. The rows should be copied below each other on the next empty line. I have written the following code and it works fine. It copies the rows only if the value in Column C = 1, 2 or 3 which is what I want. However, at the same time, I want to exclude the rows where Column A contains the strings "800M" or "1500M" or "RELAY". I have tried different things but I can't get it to work properly. I have attached a sample file. Any help would be greatly appreciated.


    [VB]
    Sheets("TrackA").Activate

    Dim bottomC As Long
    bottomC = Range("c" & Rows.count).End(xlUp).Row
    Dim cell As Range
    For Each cell In Range("C2:C" & bottomC)
    If cell.Value = 1 Or cell.Value = 2 Or cell.Value = 3 Then
    cell.EntireRow.Copy Sheets("RegionQualifiers").Cells(r, 1)
    r = r + 1
    End If
    Next cell
    [/VB]

    Files

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Copy and paste rows based on multiple criteria


    Mumps,


    I suppose by now you have read the Forum Rules? If not, please take the time to do so.
    You are required to use code tags when posting VBA code.


    Please edit your thread to add the missing code tags.


    [COLOR="navy"]How to use code tags[/COLOR]


    [noparse]

    Code
    1. [/noparse]
    2. [COLOR="navy"]your code goes between these tags[/COLOR]
    3. [noparse]

    [/noparse]


    ---------------------------------------------------------


    References to sheets are sheet code names

  • Re: Copy and paste rows based on multiple criteria


    My apologies. The problem was caused by a typo. I have fixed the code tag. Thank you so much for you suggestion, AAE. I modified your code as below and ran through it one line at a time. It worked as expected except that it didn't copy the rows to the RegionQualifiers sheet. I'm not sure what went wrong.


    [VB]
    Option Explicit

    Sub Copy_Cells()

    Dim nextrow As Long, lastrow As Long
    Dim rngData As Range

    Application.ScreenUpdating = False
    Sheets("TrackA").Unprotect

    nextrow = Sheets("RegionQualifiers").Cells(Rows.count, "A").End(xlUp).Row + 1

    With Sheets("TrackA")
    .AutoFilterMode = False
    lastrow = .Cells(Rows.count, 1).End(xlUp).Row

    .Range("H1") = "Helper"
    .Range("H2:H" & lastrow).Formula = "=OR(LEFT(A2,FIND("" "",A2,1)-1)={""800M"",""1500M"",""Relay""})"

    Set rngData = .Range("A1:H" & lastrow)

    rngData.AutoFilter field:=3, Criteria1:=">=1", Operator:=xlAnd, Criteria2:="<=3"
    rngData.AutoFilter field:=8, Criteria1:=False

    rngData.Resize(, 7).SpecialCells(xlCellTypeVisible).Copy Sheets("RegionQualifiers").Range("A" & nextrow)

    Application.CutCopyMode = False

    .AutoFilterMode = False

    .Range("H1:H" & lastrow).Clear

    End With

    Set rngData = Nothing

    Application.ScreenUpdating = True

    End Sub
    [/VB]

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Copy and paste rows based on multiple criteria


    Unless your actual workbook is different than your sample workbook, there was no need to change the code - - it ran perfectly in your sample workbook.
    If you didn't follow the link and read the page on sheet code names I suggest you do so.


    However, I copied your revised code and ran it in your sample workbook and it ran without a hitch.


    BTW: you did not re-apply worksheet protection at then end of the code.

  • Re: Copy and paste rows based on multiple criteria


    Thank you once again AAE. I changed the code only to make it easier for me to identify the sheets. The code you sent me does work perfectly in the sample workbook but for some reason it doesn't in my actual workbook which contains 12 worksheets. The sheet names are the same in both. I included only 2 sheets in the sample otherwise the file would have been too large to upload. I will continue to work with it. I am relatively new at using VBA and it has become my hobby. I truly appreciate your help and your patience.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.