View original (French)

VBA | If contains then copy else skip to next

Solved
Kirilov Posted messages 108 Status Member -  
Kirilov Posted messages 108 Status Member -
Hello,
I am working on an Excel file that I need to split across several sheets based on a criterion.
I would like to use a macro that says: If cell AD1=2 then copy/paste row 1 to sheet2 and move to the next one, AND if cell AD1=3 then copy/paste row 1 to sheet3, etc.
All of this for 4 criteria.

But I'm really hopeless at VBA and can't seem to write it.

Another possible difficulty I’ve encountered during copy/paste: some of the cells contain text that is often (frequently) longer than 255 characters, and during copy/pasting they get truncated.

Thank you very much for your help!
Best regards,
Kirilov

Configuration: Windows / Chrome 61.0.3163.100

5 answers

  1.
    gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello,

    For VBA code, you need to know the context precisely, so if you could provide us with an example of the data structure, without personal elements, along with the desired results explained clearly, it would be easier to help you.
    To share your workbook, please upload it to https://www.cjoint.com/ (instructions) and post the obtained link here.
    --
    Always stay calm
    Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry
    0
  2. Kirilov Posted messages 108 Status Member 1
     
    Hello,

    You will find the structure of the file here: http://www.cjoint.com/c/GJziCkcuipV

    Thank you for your help,

    Kirilov
    0
  3. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello,

    With this macro you should be able to make your copy. 
    Public Sub copier() Dim lig As Long With Sheets("Matrice") For lig = 2 To .Cells(Rows.Count, "AD").End(xlUp).Row If .Cells(lig, "AD") = 1 Or .Cells(lig, "AD") = 2 Then .Range("A" & lig & ":AN" & lig).Copy _ Destination:=Sheets("VAL_1").Cells(Sheets("VAL_1").Cells(Rows.Count, "A").End(xlUp).Row + 1, 1) End If Next lig End With End Sub

    If you run it multiple times, the copy will always be made to the end.
    --
    Always zen
    Perfection is attained, not when there is nothing left to add, but when there is nothing left to take away. Antoine de Saint-Exupéry
    0
  4. Kirilov Posted messages 108 Status Member 1
     
    Hello Gbinforme,

    I just tested it and unfortunately, it doesn't work.
    The macro only copies/pastes the last record that contains the value 1 in Cell AD, excluding all the others. That's the impression it gives me.

    Kirilov
    0
    1. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
       
      re
      it's because your column A is empty, try this 
      Public Sub copy() Dim row As Long With Sheets("Matrice") For row = 2 To .Cells(Rows.Count, "AD").End(xlUp).Row If .Cells(row, "AD") = 1 Or .Cells(row, "AD") = 2 Then .Range("A" & row & ":AN" & row).Copy _ Destination:=Sheets("VAL_1").Cells(Sheets("VAL_1").Cells(Rows.Count, "AD").End(xlUp).Row + 1, 1) End If Next row End With End Sub
      0
  6. Kirilov Posted messages 108 Status Member 1
     
    THANK YOU VERY MUCH.
    It works wonders and is going to save me a ton of time!

    Best regards,

    Kirilov
    0