VBA to update one sheet from data stored in another workbook

[Lorum]

Hello there!

How are you? I hope you can help me. I'm new in VBA and I have an issue I can't resolve. I'll try to explain what I need.

I have Workbook A and Workbook B.

Data in Workbook A is updated by clicking a button. It takes information from another Workbook B that is generated by an external program in extension .xlsx.

1. DAY 1. I exported my Workbook B by using my external program.

DATE	ID	TYPE	TIME
11/08/2020	80205029	BAT	10:50
11/08/2020	80206789	DAT	10:51
16/08/2020	80205029	BAT	17:12
20/08/2020	80976534	BAT	23:42
21/08/2020	80212456	TAT	07:25

2. DAY 1. Then I run my macro and I get that data into my Workbook A. The Description column is treated later and is not included in Workbook B.

DATE	ID	DESCRIPTION	TYPE	TIME
11/08/2020	80205029		BAT	10:50
11/08/2020	80206789		DAT	10:51
16/08/2020	80205029		BAT	17:12
20/08/2020	80976534		BAT	23:42
21/08/2020	80212456		TAT	07:25

2. DAY 3. I entered and complete columns in Workbook A.

DATE	ID	DESCRIPTION	TYPE	TIME
11/08/2020	80205029	Everything OK	BAT	10:50
11/08/2020	80206789	Requested	DAT	10:51
16/08/2020	80205029	Everything OK	BAT	17:12
20/08/2020	80976534	Nothing to do	BAT	23:42
21/08/2020	80212456	Requested	TAT	07:25

3. DAY 7. The program has generated a new Workbook B with new data. Some new rows could have been added in Workbook B, and even some cells in the TYPE column has been changed for one item already inserted in Workbook A.

DATE	ID	TYPE	TIME
11/08/2020	80205029	BAT	10:50
11/08/2020	80206789	BAT	14:07
11/08/2020	80206789	DAT	10:51
18/08/2020	80003567	DAT	13:18
20/08/2020	80976534	TAT	23:42
21/08/2020	80212456	TAT	07:25
22/08/2020	80009345	BAT	05:52
24/08/2020	80195642	DAT	11:24

Notice what happened:

• A new line appeared in ROW 2 with same day, same ID but it is a different TIME, not necessarily sorted.
• The ROW containing DATE 16/08/2020 and ID 80205029 has disappeared.
• A new line appeared in ROW 4 with new data.
• The TYPE cell has changed its value for ROW 5.
• New lines appear at the end.

What I want is Worksheet A to be updated like this:

DATE	ID	DESCRIPTION	TYPE	TIME
11/08/2020	80205029	Everything OK	BAT	10:50
11/08/2020	80206789		BAT	14:07
11/08/2020	80206789	Requested	DAT	10:51
18/08/2020	80003567		DAT	13:18
20/08/2020	80976534	Nothing to do	TAT	23:42
21/08/2020	80212456	Requested	TAT	07:25
22/08/2020	80009345		BAT	05:52
24/08/2020	80195642		DAT	11:24

So I want the update to add new rows in the right order, not to delete the rows that have been previously inserted and have disappeared in the new Worksheet B exported, and to change the values of those cells that have changed in the TYPE column.

I have tried plenty of things, the last was a double FOR but it is bad coding.

Sub Update()

Dim lastRowScr As Integer, lastRowLocal As Integer, nRowsSrc As Integer, nRowsLocal As Integer, x As Integer, _
y As Integer

    Application.ScreenUpdating = False
    
       Set closedBook = Workbooks.Open("C:\Users\mfortesg\Documents\Suivi d'Analyses AT\Projet - Automatisation\BO\BO.xlsx")
      
       lastRowScr = closedBook.Sheets(1).Cells.Find(What:="*", SearchDirection:=xlPrevious).Row - 2
       nRowsScr = lastRowScr - 16
      
       lastRowLocal = ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells.Find(What:="*", SearchDirection:=xlPrevious).Row
       nRowsLocal = lastRowLocal - 2
      
       If nRowsLocal = 0 Then
      
            For x = 17 To lastRowScr
            
                y = x - 14
                        
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 1).Value = closedBook.Sheets(1).Cells(x, 1).Value
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 2).Value = closedBook.Sheets(1).Cells(x, 2).Value
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 3).Value = closedBook.Sheets(1).Cells(x, 3).Value
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 4).Value = closedBook.Sheets(1).Cells(x, 4).Value
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 5).Value = closedBook.Sheets(1).Cells(x, 17).Value
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 6).Value = closedBook.Sheets(1).Cells(x, 11).Value
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 7).Value = closedBook.Sheets(1).Cells(x, 10).Value
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 8).Value = closedBook.Sheets(1).Cells(x, 26).Value
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 24).Value = Replace(closedBook.Sheets(1).Cells(x, 28).Value, ",", ":")
            
            Next x
            
            MsgBox ("Le Tableau d'Analyse AT a été mis à jour correctement.")
        
            Else
            
            For x = 17 To lastRowScr
    
                For y = x - 14 To lastRowLocal + 1
                    
                    If ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 1).Value = closedBook.Sheets(1).Cells(x, 1).Value And _
                       ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 2).Value = closedBook.Sheets(1).Cells(x, 2).Value And _
                       ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 24).Value = Replace(closedBook.Sheets(1).Cells(x, 28).Value, ",", ":") Then
                    
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 6).Value = closedBook.Sheets(1).Cells(x, 11).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 5).Value = closedBook.Sheets(1).Cells(x, 17).Value
                        
                    ElseIf y = lastRowLocal + 1 Then
                                    
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 1).Value = closedBook.Sheets(1).Cells(x, 1).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 2).Value = closedBook.Sheets(1).Cells(x, 2).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 3).Value = closedBook.Sheets(1).Cells(x, 3).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 4).Value = closedBook.Sheets(1).Cells(x, 4).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 5).Value = closedBook.Sheets(1).Cells(x, 17).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 6).Value = closedBook.Sheets(1).Cells(x, 11).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 7).Value = closedBook.Sheets(1).Cells(x, 10).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 8).Value = closedBook.Sheets(1).Cells(x, 26).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 24).Value = Replace(closedBook.Sheets(1).Cells(x, 28).Value, ",", ":")
                    
                    End If
                    
                Next y
            
            Next x
            
            MsgBox ("Le Tableau d'Analyse AT a été mis à jour correctement.")
        
        End If
            
       closedBook.Close SaveChanges:=False

    
    Application.ScreenUpdating = True

End Sub

Can anyone help me? Please!

Thanks in advance.