Calling a1ehouse

[Lennon]

Hey man ... hoping you can help me with Excel again ... (anyone else can too) Ok. This is what I want to do. http://en.allexperts.com/q/Excel-1059/EXCEL-VBA-FIND-COPY-1.htm I tryed to modify the formula given in the answer for myself but when I run the macro the result I get is "0 Finished rows copied" Here's my formula ... Sub CopyFinished() 'Copy cells of cols A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK from rows containing "Finished" in 'col C of the active worksheet (source sheet) to cola 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK of Results (destination sheet) Dim DestSheet As Worksheet Set DestSheet = Worksheets("Results") Dim aRow As Long 'row index on source worksheet Dim cRow As Long 'row index on destination worksheet Dim aCount As Long sCount = 0 cRow = 1

For sRow = 3 To Range("C65536").End(xlUp).Row
     'use pattern matching to find "Finished" anywhere in cell
     If Cells(sRow, "C") Like "*Finished*" Then
        sCount = sCount + 1
        cRow = cRow + 1
        'copy cols A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK
        Cells(sRow, "A").Copy Destination:=DestSheet.Cells(cRow, "A")
        Cells(sRow, "B").Copy Destination:=DestSheet.Cells(cRow, "B")
        Cells(sRow, "C").Copy Destination:=DestSheet.Cells(cRow, "C")
        Cells(sRow, "D").Copy Destination:=DestSheet.Cells(cRow, "D")
        Cells(sRow, "E").Copy Destination:=DestSheet.Cells(cRow, "E")
        Cells(sRow, "F").Copy Destination:=DestSheet.Cells(cRow, "F")
        Cells(sRow, "G").Copy Destination:=DestSheet.Cells(cRow, "G")
        Cells(sRow, "H").Copy Destination:=DestSheet.Cells(cRow, "H")
        Cells(sRow, "I").Copy Destination:=DestSheet.Cells(cRow, "I")
        Cells(sRow, "J").Copy Destination:=DestSheet.Cells(cRow, "J")
        Cells(sRow, "K").Copy Destination:=DestSheet.Cells(cRow, "K")
        Cells(sRow, "L").Copy Destination:=DestSheet.Cells(cRow, "L")
        Cells(sRow, "M").Copy Destination:=DestSheet.Cells(cRow, "M")
        Cells(sRow, "N").Copy Destination:=DestSheet.Cells(cRow, "N")
        Cells(sRow, "O").Copy Destination:=DestSheet.Cells(cRow, "O")
        Cells(sRow, "P").Copy Destination:=DestSheet.Cells(cRow, "P")
        Cells(sRow, "Q").Copy Destination:=DestSheet.Cells(cRow, "Q")
        Cells(sRow, "R").Copy Destination:=DestSheet.Cells(cRow, "R")
        Cells(sRow, "S").Copy Destination:=DestSheet.Cells(cRow, "S")
        Cells(sRow, "T").Copy Destination:=DestSheet.Cells(cRow, "T")
        Cells(sRow, "U").Copy Destination:=DestSheet.Cells(cRow, "U")
        Cells(sRow, "V").Copy Destination:=DestSheet.Cells(cRow, "V")
        Cells(sRow, "W").Copy Destination:=DestSheet.Cells(cRow, "W")
        Cells(sRow, "X").Copy Destination:=DestSheet.Cells(cRow, "X")
        Cells(sRow, "Y").Copy Destination:=DestSheet.Cells(cRow, "Y")
        Cells(sRow, "Z").Copy Destination:=DestSheet.Cells(cRow, "Z")
        Cells(sRow, "AA").Copy Destination:=DestSheet.Cells(cRow, "AA")
        Cells(sRow, "AB").Copy Destination:=DestSheet.Cells(cRow, "AB")
        Cells(sRow, "AC").Copy Destination:=DestSheet.Cells(cRow, "AC")
        Cells(sRow, "AD").Copy Destination:=DestSheet.Cells(cRow, "AD")
        Cells(sRow, "AE").Copy Destination:=DestSheet.Cells(cRow, "AE")
        Cells(sRow, "AF").Copy Destination:=DestSheet.Cells(cRow, "AF")
        Cells(sRow, "AG").Copy Destination:=DestSheet.Cells(cRow, "AG")
        Cells(sRow, "AH").Copy Destination:=DestSheet.Cells(cRow, "AH")
        Cells(sRow, "AI").Copy Destination:=DestSheet.Cells(cRow, "AI")
        Cells(sRow, "AJ").Copy Destination:=DestSheet.Cells(cRow, "AJ")
        Cells(sRow, "AK").Copy Destination:=DestSheet.Cells(cRow, "AK")
     End If
  Next sRow
  
  MsgBox sCount & " Finished rows copied", vbInformation, "Transfer Done"

End Sub

Also .. if it's possible, it would be great if you could make it cut instead of copy ... and also would it be possible for the macro to check for new cells with the word "Finished" automatically once a day and cut/paste those rows into the "Results" worksheet?

[Lennon]

Re: Calling a1ehouse Woops ... this was supposed to be posted in the tools and general betting forum .. mods can you move it there please?