Macro VBA for Exel - copy data from one sheet to another with one condition

Question

Hurray for you guys, I’m completely new to VBA and a major programmer. I need help to build a macro for Excel.

Context: In the Excel book that is found in the following link, I intend to develop a macro in VBA that fills the fields "transatas", "finalizadas" and "wait" of the sheet "Report", with the information contained in the fields "num transatas", "num finalizadas" and "num espera" of the sheet "Dia18set".

Problem: The "Dia18set" information is periodically produced completely randomly. Each time the sheet is produced, the "process codes" are not always the same, nor appear in the same order.

Therefore, in the macro I have to check, first, if there is the "Process Code" that I intend to fill in the sheet "Report".

If the "process code" of the sheet "Report" does not exist in the sheet "Dia18set" does not fill anything.

If it exists, the routine will have to fetch the values " in a transatas", "in a finalized" and " in a waiting" present in the "Dia18set" sheet and automatically fill in the respective fields of the "Report" sheet.

Attached, follow the Excel file and the code file I developed, but it is not working.

Thanks for the help.

Excel file link -> https://www.sendspace.com/file/m2tvbb

Code developed

Sub transferirDados()
Dim tarefasRecebidas As Long
Dim tarefasProcessadas As Long
Dim tarefasPendentes As Long

Dim IdProcesso As String

Dim ultimaLinhaDia18Set As Long
Dim ultimalinhaRelatorio As Long

Dim contLinhasDia18Set As Long
Dim contLinhasRelatorio As Long

 'Definir numero total de linhas da folha "Relatorio"
ultimalinhaRelatorio = Sheets("Relatorio").Range("A" & Rows.Count).End(xlUp).Row

'Definir numero total de linhas da folha "Dia18Set"
ultimaLinhaDia18Set = Sheets("Dia18Set").Range("A" & Rows.Count).End(xlUp).Row



' O "primeiro For" itera cada umas das linhas da folha "Relatorio" a partir da linha 3 para obter cada um dos "IdProcesso"
For contLinhasRelatorio = 3 To ultimalinhaRelatorio

    IdProcesso = Sheets("Relatorio").Cells(contLinhasRelatorio, 1)

' O segundo For itera cada uma das linhas da folha "Dia18Set" a partir da linha 2, para obter cada um dos "IdProcesso"
    For contLinhasDia18Set = 2 To ultimaLinhaDia18Set

        ' Para cada linha da folha "Dia18Set", verifica se o "Id do processo" da folha "Dia18Set" é igual ao "Id do processo" que esté em análise no "primeiro For"
        If Sheets("Dia18Set").Cells(contLinhasDia18Set, 1) Like IdProcesso Then

        'Caso o "IDprocesso" da folha "Dia18Set" seja igual ao da folha "Relatorio", atribui o valor da respetiva linha e coluna às variáves "tarefasRecebidas","tarefasProcessadas" e "tarefasPendentes"
            tarefasRecebidas = Sheets("Dia18Set").Cells(contLinhasDia18Set, 2)
            tarefasProcessadas = Sheets("Dia18Set").Cells(contLinhasDia18Set, 3)
            tarefasPendentes = Sheets("Dia18Set").Cells(contLinhasDia18Set, 4)

            'Atribui à célula do livro "Relatorio" o numero das respetivas tarefas.
            Sheets("Relatorio").Cells(contLinhasRelatorio, 2) = tarefasRecebidas
            Sheets("Relatorio").Cells(contLinhasRelatorio, 3) = tarefasProcessadas
            Sheets("Relatorio").Cells(contLinhasRelatorio, 4) = tarefasPendentes
        End If
    Next contLinhasDia18Set
Next contLinhasRelatorio
End Sub

Answer 1

If the Idprocesso needs to be exactly the same, you do not use the Like, because the same is usually used to get partial cards with wildcards. So, If Sheets("Dia18Set").Cells(contLinhasDia18Set, 1) Like IdProcesso Then may be changed to:

If Sheets("Dia18Set").Cells(contLinhasDia18Set, 1) = IdProcesso Then

Note:This code worked for me with Like or =

In the Dia18set Spreadsheet:

+---+--------+-------+---+----+
|   |   A    |   B   | C | D  |
+---+--------+-------+---+----+
| 2 | cod_3  | 10000 | 1 |  4 |
| 3 | cod_4  |  9000 | 2 |  5 |
| 4 | cod_5  |  8000 | 3 |  6 |
| 5 | cod_1  |  7000 | 4 |  7 |
| 6 | cod_7  |  6000 | 5 |  8 |
| 7 | cod_8  |  5000 | 6 |  9 |
| 8 | cod_9  |  4000 | 7 | 10 |
| 9 | cod_10 |  3000 | 8 | 11 |
+---+--------+-------+---+----+

Produced this result:

+----+-----------------+-----------+-------------+----------+
|    |        A        |     B     |      C      |    D     |
+----+-----------------+-----------+-------------+----------+
|  1 |                 | 18/set    |             |          |
|  2 | Código Processo | transatas | finalizadas | aguardar |
|  3 | cod_1           | 7000      | 4           | 7        |
|  4 | cod_2           |           |             |          |
|  5 | cod_3           | 10000     | 1           | 4        |
|  6 | cod_4           | 9000      | 2           | 5        |
|  7 | cod_5           | 8000      | 3           | 6        |
|  8 | cod_6           |           |             |          |
|  9 | cod_7           | 6000      | 5           | 8        |
| 10 | cod_8           | 5000      | 6           | 9        |
| 11 | cod_9           | 4000      | 7           | 10       |
| 12 | cod_10          | 3000      | 8           | 11       |
+----+-----------------+-----------+-------------+----------+

Next time, try sending examples of your tables by ASCII Table Generator

.