Copie to

[brankscaffold]

I got this one off the net, but I can't get it to work quite right.

now only 2 lines are copied and always the same

Sub rowcopy()
Const FirstRow = 2
Dim rij As Long
Dim n As Long
Dim src As Worksheet
Dim trg As Worksheet
Dim SrcRow As Long
Dim lastRow As Long
                        Set src = Sheets("Ridon 22")
                        Set trg = Sheets("Asindo 22")
                                                        Application.ScreenUpdating = False

                        rij = trg.[A65536].End(xlUp).Row

For n = 5 To Blad1.[A65536].End(xlUp).Row
If Cells(n, "AE").Value = "Asindo" Then
Range(Cells(n, "A"), Cells(n, "AZ")).Copy
trg.Cells(rij, "A").PasteSpecial

 

[brankscaffold]

ElseIf src.Cells(n, "B" And "E" And "H" And "AD").Value <> src.Cells(n - 1, "B" And "E" And "H" And "AD").Value Then
                OKToCopy = "Y"
          '  ElseIf src.Cells(n, "E").Value <> src.Cells(n - 1, "E").Value Then
          '      OKToCopy = "Y"
          '  ElseIf src.Cells(n, "H").Value <> src.Cells(n - 1, "H").Value Then
          '      OKToCopy = "Y"
           ' ElseIf src.Cells(n, "AD").Value <> src.Cells(n - 1, "AD").Value Then
          '      OKToCopy = "Y"

you're the best.
I made 1 line of everything with AND in it, so that it transfers the data, for example, the same invoice number but a different name. he didn't transfer that at first and now he does

 

[Alex Blakenburg]

I made 1 line of everything with AND in it

I had considered that but someone suggested once that it would be faster (and I don't by how much) if it didn't have to evaluate all the if expressions, which it has to when you join them with and AND. So the aim I guess is to put the most likely to be the decider first, so it only has to do the others infrequently.

Thanks for letting me know it is working for you and glad I could help.

 

[smozgur]

you're the best.

So, the answer post is #20. Therefore, I changed the marked solution accordingly.

@brankscaffold - As far as I know, the Cells property requires either column index number or column letters as a string (like 2 or "B"). You should be getting an error with the following usage unless you have an On Error statement that will silence the error.

src.Cells(n, "B" And "E" And "H" And "AD").Value

Note: I've never seen such usage, and it shouldn't work unless the operands are either boolean or numeric that could be cast as an operand which will only return the wrong column number in the second case. However, I am very much interested to learn if this is a known method that I've never seen before.

However, if you'd like to shorten the code, then you can use OR operator as shown below:

If n = FirstRow Or _
            src.Cells(n, "B").Value <> src.Cells(n - 1, "B").Value Or _
            src.Cells(n, "E").Value <> src.Cells(n - 1, "E").Value Or _
            src.Cells(n, "H").Value <> src.Cells(n - 1, "H").Value Or _
            src.Cells(n, "AD").Value <> src.Cells(n - 1, "AD").Value Then
    OKToCopy = "Y"
Else
    OKToCopy = "N"
End If

 

[brankscaffold]

So, the answer post is #20. Therefore, I changed the marked solution accordingly.

@brankscaffold - As far as I know, the Cells property requires either column index number or column letters as a string (like 2 or "B"). You should be getting an error with the following usage unless you have an On Error statement that will silence the error.

src.Cells(n, "B" And "E" And "H" And "AD").Value

Note: I've never seen such usage, and it shouldn't work unless the operands are either boolean or numeric that could be cast as an operand which will only return the wrong column number in the second case. However, I am very much interested to learn if this is a known method that I've never seen before.

However, if you'd like to shorten the code, then you can use OR operator as shown below:

If n = FirstRow Or _
            src.Cells(n, "B").Value <> src.Cells(n - 1, "B").Value Or _
            src.Cells(n, "E").Value <> src.Cells(n - 1, "E").Value Or _
            src.Cells(n, "H").Value <> src.Cells(n - 1, "H").Value Or _
            src.Cells(n, "AD").Value <> src.Cells(n - 1, "AD").Value Then
    OKToCopy = "Y"
Else
    OKToCopy = "N"
End If

with 4 of the same data it is a duplicate and they will not copy or move. if there is a difference, these must be copied / moved.
if I enter your code (maybe I did it wrong) then it only creates 1 sheet and copies only 1 line to it

ElseIf src.Cells(n, "B" And "E" And "H" And "AD").Value <> src.Cells(n - 1, "B" And "E" And "H" And "AD").Value Then

no matter what I try this line works so far, and luckily I don't get a message yet

thanks for the input

 

[brankscaffold]

So, the answer post is #20. Therefore, I changed the marked solution accordingly.

@brankscaffold - As far as I know, the Cells property requires either column index number or column letters as a string (like 2 or "B"). You should be getting an error with the following usage unless you have an On Error statement that will silence the error.

src.Cells(n, "B" And "E" And "H" And "AD").Value

Note: I've never seen such usage, and it shouldn't work unless the operands are either boolean or numeric that could be cast as an operand which will only return the wrong column number in the second case. However, I am very much interested to learn if this is a known method that I've never seen before.

However, if you'd like to shorten the code, then you can use OR operator as shown below:

If n = FirstRow Or _
            src.Cells(n, "B").Value <> src.Cells(n - 1, "B").Value Or _
            src.Cells(n, "E").Value <> src.Cells(n - 1, "E").Value Or _
            src.Cells(n, "H").Value <> src.Cells(n - 1, "H").Value Or _
            src.Cells(n, "AD").Value <> src.Cells(n - 1, "AD").Value Then
    OKToCopy = "Y"
Else
    OKToCopy = "N"
End If

as you said my adjustment worked temporarily.
thanks for the input

 

[brankscaffold]

I also need a check for already copied data on the already existing sheets also with the same search equations.

can you help point me in the right direction
Or should I create a new topic for this?

 

[Alex Blakenburg]

You would need to create a new topic for that.

If you simply wanted to check whether was data below the headings on the target sheet, that would be fairly straight forward but

with the same search equations.

indicates you want some sort of criteria applied, the won't be a trivial exercise.