loop: copy if
Results 1 to 3 of 3

Thread: loop: copy if

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question loop: copy if

    hi
    i want a code which check second colulm and if to right charcter was 01 then copy entire row to another sheet
    for example if b2=9164801 then copy row 2 in sheet2 and do it again until the last row
    can anybody help me on it
    i would appreciate you

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,360
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: loop: copy if

    Does this do what you want? Change destination sheet name to suit - assumes destination sheet already exists.
    Code:
    Sub CopyIf01()
    Dim c As Range, NxRw As Long
    Application.ScreenUpdating = False
    For Each c In Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
        If c.Value Like "*01" Then
           With Sheets("Sheet3")
                If IsEmpty(.Range("A1")) Then
                    NxRw = 1
                Else
                    NxRw = .Cells(Rows.Count, "A").End(xlUp).Row + 1
                End If
            End With
            Intersect(c.EntireRow, ActiveSheet.UsedRange).Copy Destination:=Sheets("Sheet3").Cells(NxRw, "A")
        End If
    Next c
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: loop: copy if

    Hi,
    Check this out.

    Code:
    Sub CopyRows()
    Dim ws1 as worksheet
    Dim ws2 ad worksheet
    Dim i&
    Dim counter&
    
    Set ws1=worksheets(1)
    Set ws2=worksheets(2)
    i=2
    With ws1
    Do while .cells(i, "B")<>""
        If cstr(right(.cells(i,"B"),2))="01" then
             counter=counter+1
            .cells(i,"B").EntireRow.copy ws2.cells(counter,"A")
        End if
        i=i+1
    Loop
    end with
    
    Set ws1=nothing
    Set ws2= nothing
    End sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •