[Solved] Copy Missing Date from One file to another

J

john7777

New member
I have 2 Excel file and I want to update ZYZ file with ABC file missing item with its related date and quantity from one file to another.

I have a macro which copy only item field. I have to update missing item with its related columns (A, and C)
 

Attachments

Hello john7777,
Welcome to ExcelDemy Forum and thanks for sharing your problem with us. I understand that you want to copy data from the ABC file which is missing in the XYZ file and paste it into the XYZ file.

You used ADO Database properties to copy missing data from. However, many parameters for the Database SQL query were not given in your code. Hence, I have tried something different.​

Open the XYZ file, go to the Developer tab, and click the Visual Basic option.

2uRdXFfuwM84R98K4ckKHtxjZWlZDR-ZmBTwbcOHSOVKkoIsEsBEJaDLeU4g_9CxLEgnymXzUv_Na6VsKvaM7TKlRJPXUUjVOMCQXD971gQPAZb12fg6X9zgWCyVcgLATbwFoucrP6AnGK2Kaz_H5K4

​
Insert a Module and enter the following code.

Code: 
Sub Update_XYZ_File()
   
    Set XYZ_file = ThisWorkbook
    Set ABC_file = Workbooks.Open("C:\Users\ASUS\Downloads\ABC.xlsm") 'provide file path of your required file
   
    Set XYZfile_sheet = XYZ_file.Sheets("Purchase") ' provide the sheet name from your file
    Set ABCfile_sheet = ABC_file.Sheets("Sheet2") ' provide the sheet name from your file
   
    Dim ABClast_row As Integer
    ABClast_row = ABCfile_sheet.Cells(Rows.Count, 2).End(xlUp).Row
    Set ABCsheet_range = ABCfile_sheet.Range("A3:C" & ABClast_row)
   
    Dim i As Integer
    Dim j As Integer
    Dim data_match As Boolean
    Dim XYZlast_row As Integer
    Dim ABC_data As String
    Dim XYZ_data As String
    Dim last_row As Integer
   
    Application.DisplayAlerts = False
   
    Set missing_data = Nothing
   
    For i = 1 To ABCsheet_range.Rows.Count
        data_match = False
        XYZlast_row = XYZfile_sheet.Cells(Rows.Count, 2).End(xlUp).Row
        Set XYZsheet_range = XYZfile_sheet.Range("A3:C" & XYZlast_row)
        ABC_data = ""
        ABC_data = JoinRow(ABCsheet_range.Rows(i))
        For j = 1 To XYZsheet_range.Rows.Count
            XYZ_data = ""
            XYZ_data = JoinRow(XYZsheet_range.Rows(j))
            If ABC_data = XYZ_data Then
                data_match = True
                Exit For
            End If
        Next j
        If data_match = False Then
            If missing_data Is Nothing Then
                Set missing_data = ABCsheet_range.Rows(i)
            Else
                Set missing_data = Union(missing_data, ABCsheet_range.Rows(i))
            End If
        End If
    Next i
   
    missing_data.Copy
    XYZfile_sheet.Cells(XYZlast_row + 1, 1).PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False
   
    Application.DisplayAlerts = True
    ABC_file.Close SaveChanges:=False

End Sub
Function JoinRow(row_range As Range) As String
    Dim cell As Range
    Dim result As String

    For Each cell In row_range.Cells
        result = result & "," & cell.Value
    Next cell

    JoinRow = result
End Function
Note: Here, the Display Alerts are turned off during the Copy-Paste operation as the “MyList” Named Range in your workbooks was obstructing the Copy-Paste Operation.

Afterward, Save and Run the Subprocedure. The following video provides an overview of the Copy-Paste operation and the output.


I hope this resolves your problem. Let us know your feedback. The XYZ workbook is attached below.

Regards,
Seemanto Saha
ExcelDemy
 

Attachments

Thanks for your kind help, I want one more help
My both file at H:\New folder (2)\New folder
I try to change the path but showing error How to change the path
 
Dear john7777,
Thanks for your feedback. If the ABC.xlsm file is in H:\New folder (2)\New folder directory then you can apply the following code:
Code: 
Sub Update_XYZ_File()
 
    Set XYZ_file = ThisWorkbook
    Set ABC_file = Workbooks.Open("H:\New folder (2)\New folder\ABC.xlsm") 'provide file path of your required file
 
    Set XYZfile_sheet = XYZ_file.Sheets("Purchase") ' provide the sheet name from your file
    Set ABCfile_sheet = ABC_file.Sheets("Sheet2") ' provide the sheet name from your file
 
    Dim ABClast_row As Integer
    ABClast_row = ABCfile_sheet.Cells(Rows.Count, 2).End(xlUp).Row
    Set ABCsheet_range = ABCfile_sheet.Range("A3:C" & ABClast_row)
 
    Dim i As Integer
    Dim j As Integer
    Dim data_match As Boolean
    Dim XYZlast_row As Integer
    Dim ABC_data As String
    Dim XYZ_data As String
    Dim last_row As Integer
 
    Application.DisplayAlerts = False
 
    Set missing_data = Nothing
 
    For i = 1 To ABCsheet_range.Rows.Count
        data_match = False
        XYZlast_row = XYZfile_sheet.Cells(Rows.Count, 2).End(xlUp).Row
        Set XYZsheet_range = XYZfile_sheet.Range("A3:C" & XYZlast_row)
        ABC_data = ""
        ABC_data = JoinRow(ABCsheet_range.Rows(i))
        For j = 1 To XYZsheet_range.Rows.Count
            XYZ_data = ""
            XYZ_data = JoinRow(XYZsheet_range.Rows(j))
            If ABC_data = XYZ_data Then
                data_match = True
                Exit For
            End If
        Next j
        If data_match = False Then
            If missing_data Is Nothing Then
                Set missing_data = ABCsheet_range.Rows(i)
            Else
                Set missing_data = Union(missing_data, ABCsheet_range.Rows(i))
            End If
        End If
    Next i
 
    missing_data.Copy
    XYZfile_sheet.Cells(XYZlast_row + 1, 1).PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False
 
    Application.DisplayAlerts = True
    ABC_file.Close SaveChanges:=False

End Sub
Function JoinRow(row_range As Range) As String
    Dim cell As Range
    Dim result As String

    For Each cell In row_range.Cells
        result = result & "," & cell.Value
    Next cell

    JoinRow = result
End Function

Regards,
Seemanto Saha
ExcelDemy
 
Once we run the Macro and copy the missing data then again we run the macro it shows error in the following line

missing_data.Copy
 
Hello john7777,
Glad to hear that the macro worked properly the first time. However, after executing the macro for the second time, no missing data was found. Hence, there was an error while copying the missing_data range.
We can add a simple If statement to check for an empty missing_data range and skip the copy operation to avoid errors. Here is the updated code:
Code: 
Sub Update_XYZ_File()
 
    Set XYZ_file = ThisWorkbook
    Set ABC_file = Workbooks.Open("H:\New folder (2)\New folder\ABC.xlsm") 'provide file path of your required file
 
    Set XYZfile_sheet = XYZ_file.Sheets("Purchase") ' provide the sheet name from your file
    Set ABCfile_sheet = ABC_file.Sheets("Sheet2") ' provide the sheet name from your file
 
    Dim ABClast_row As Integer
    ABClast_row = ABCfile_sheet.Cells(Rows.Count, 2).End(xlUp).Row
    Set ABCsheet_range = ABCfile_sheet.Range("A3:C" & ABClast_row)
 
    Dim i As Integer
    Dim j As Integer
    Dim data_match As Boolean
    Dim XYZlast_row As Integer
    Dim ABC_data As String
    Dim XYZ_data As String
    Dim last_row As Integer
 
    Application.DisplayAlerts = False
 
    Set missing_data = Nothing
 
    For i = 1 To ABCsheet_range.Rows.Count
        data_match = False
        XYZlast_row = XYZfile_sheet.Cells(Rows.Count, 2).End(xlUp).Row
        Set XYZsheet_range = XYZfile_sheet.Range("A3:C" & XYZlast_row)
        ABC_data = ""
        ABC_data = JoinRow(ABCsheet_range.Rows(i))
        For j = 1 To XYZsheet_range.Rows.Count
            XYZ_data = ""
            XYZ_data = JoinRow(XYZsheet_range.Rows(j))
            If ABC_data = XYZ_data Then
                data_match = True
                Exit For
            End If
        Next j
        If data_match = False Then
            If missing_data Is Nothing Then
                Set missing_data = ABCsheet_range.Rows(i)
            Else
                Set missing_data = Union(missing_data, ABCsheet_range.Rows(i))
            End If
        End If
    Next i
   
    If missing_data Is Nothing Then
        MsgBox "No missing data found!"
    Else
        missing_data.Copy
        XYZfile_sheet.Cells(XYZlast_row + 1, 1).PasteSpecial Paste:=xlPasteAll
        Application.CutCopyMode = False
    End If
 
    Application.DisplayAlerts = True
    ABC_file.Close SaveChanges:=False

End Sub
Function JoinRow(row_range As Range) As String
    Dim cell As Range
    Dim result As String

    For Each cell In row_range.Cells
        result = result & "," & cell.Value
    Next cell

    JoinRow = result
End Function

Regards,
Seemanto Saha
ExcelDemy
 
Can I ask only one more question , If I want to change the range D3:F for both files then how to change the macro.

Thank you so much for your kind help.
 
Hello john7777,
To change the range D3:F for both files, you have to modify the ranges. Here is the modified code:

Code: 
Sub Update_XYZ_File()
 
    Set XYZ_file = ThisWorkbook
    Set ABC_file = Workbooks.Open("H:\New folder (2)\New folder\ABC.xlsm") 'provide file path of your required file
 
    Set XYZfile_sheet = XYZ_file.Sheets("Purchase") ' provide the sheet name from your file
    Set ABCfile_sheet = ABC_file.Sheets("Sheet2") ' provide the sheet name from your file
 
    Dim ABClast_row As Integer
    ABClast_row = ABCfile_sheet.Cells(Rows.Count, 5).End(xlUp).Row
    Set ABCsheet_range = ABCfile_sheet.Range("D3:F" & ABClast_row)
 
    Dim i As Integer
    Dim j As Integer
    Dim data_match As Boolean
    Dim XYZlast_row As Integer
    Dim ABC_data As String
    Dim XYZ_data As String
    Dim last_row As Integer
 
    Application.DisplayAlerts = False
 
    Set missing_data = Nothing
 
    For i = 1 To ABCsheet_range.Rows.Count
        data_match = False
        XYZlast_row = XYZfile_sheet.Cells(Rows.Count, 5).End(xlUp).Row
        Set XYZsheet_range = XYZfile_sheet.Range("D3:F" & XYZlast_row)
        ABC_data = ""
        ABC_data = JoinRow(ABCsheet_range.Rows(i))
        For j = 1 To XYZsheet_range.Rows.Count
            XYZ_data = ""
            XYZ_data = JoinRow(XYZsheet_range.Rows(j))
            If ABC_data = XYZ_data Then
                data_match = True
                Exit For
            End If
        Next j
        If data_match = False Then
            If missing_data Is Nothing Then
                Set missing_data = ABCsheet_range.Rows(i)
            Else
                Set missing_data = Union(missing_data, ABCsheet_range.Rows(i))
            End If
        End If
    Next i
  
    If missing_data Is Nothing Then
        MsgBox "No missing data found!"
    Else
        missing_data.Copy
        XYZfile_sheet.Cells(XYZlast_row + 1, 4).PasteSpecial Paste:=xlPasteAll
        Application.CutCopyMode = False
    End If
 
    Application.DisplayAlerts = True
    ABC_file.Close SaveChanges:=False

End Sub
Function JoinRow(row_range As Range) As String
    Dim cell As Range
    Dim result As String

    For Each cell In row_range.Cells
        result = result & "," & cell.Value
    Next cell

    JoinRow = result
End Function


Regards,
Seemanto Saha
ExcelDemy
 
Seemanto Saha said:
Hello john7777,
To change the range D3:F for both files, you have to modify the ranges. Here is the modified code:

Code: 
Sub Update_XYZ_File()
 
    Set XYZ_file = ThisWorkbook
    Set ABC_file = Workbooks.Open("H:\New folder (2)\New folder\ABC.xlsm") 'provide file path of your required file
 
    Set XYZfile_sheet = XYZ_file.Sheets("Purchase") ' provide the sheet name from your file
    Set ABCfile_sheet = ABC_file.Sheets("Sheet2") ' provide the sheet name from your file
 
    Dim ABClast_row As Integer
    ABClast_row = ABCfile_sheet.Cells(Rows.Count, 5).End(xlUp).Row
    Set ABCsheet_range = ABCfile_sheet.Range("D3:F" & ABClast_row)
 
    Dim i As Integer
    Dim j As Integer
    Dim data_match As Boolean
    Dim XYZlast_row As Integer
    Dim ABC_data As String
    Dim XYZ_data As String
    Dim last_row As Integer
 
    Application.DisplayAlerts = False
 
    Set missing_data = Nothing
 
    For i = 1 To ABCsheet_range.Rows.Count
        data_match = False
        XYZlast_row = XYZfile_sheet.Cells(Rows.Count, 5).End(xlUp).Row
        Set XYZsheet_range = XYZfile_sheet.Range("D3:F" & XYZlast_row)
        ABC_data = ""
        ABC_data = JoinRow(ABCsheet_range.Rows(i))
        For j = 1 To XYZsheet_range.Rows.Count
            XYZ_data = ""
            XYZ_data = JoinRow(XYZsheet_range.Rows(j))
            If ABC_data = XYZ_data Then
                data_match = True
                Exit For
            End If
        Next j
        If data_match = False Then
            If missing_data Is Nothing Then
                Set missing_data = ABCsheet_range.Rows(i)
            Else
                Set missing_data = Union(missing_data, ABCsheet_range.Rows(i))
            End If
        End If
    Next i
 
    If missing_data Is Nothing Then
        MsgBox "No missing data found!"
    Else
        missing_data.Copy
        XYZfile_sheet.Cells(XYZlast_row + 1, 4).PasteSpecial Paste:=xlPasteAll
        Application.CutCopyMode = False
    End If
 
    Application.DisplayAlerts = True
    ABC_file.Close SaveChanges:=False

End Sub
Function JoinRow(row_range As Range) As String
    Dim cell As Range
    Dim result As String

    For Each cell In row_range.Cells
        result = result & "," & cell.Value
    Next cell

    JoinRow = result
End Function


Regards,
Seemanto Saha
ExcelDemy
Click to expand...
Thanks a lot for your kind help.
I tested the above code in my orginal file with 400 rows , day before yesterday then I noticed that all records not copiping. Some rocords are missing. What will be the reason Kindly please check .


Thanks in advance
 
Dear john7777,
After filtering the ABC and XYZ files, I found 226 missing data. As the XYZ file had 189 rows before copying the missing rows, there should have been 226+189 = 415 rows after copying the missing rows. However, among the 226 missing rows, 4 rows were present twice.
​
For example, Row 248 and Row 394 are completely identical.

G0oaJJhzoWQvC9wPhZNMM_m1Z5c639wZlN69mewoX_4Kt14IHeNUiuRkmyMWQIZR1G_h4nFKgjP5IzP7Yk9bz2PuVuhZdWgK1mGIO4quwgTTBFOGY53ys0bOiKPesJpa8Z6ZwgdI9BWUrRxANQHXxg0

​
Similarly, Row 261 and Row 380, Row 261 and Row 382, Row 262 and Row 384 are identical.

As the previous VBA code was selecting range dynamically and 4 rows are present twice, 226-4 = 222 rows were copied. Hence, the XYZ file has 411 rows after copying the missing rows.​

If you want to copy all the missing rows irrespective of the duplication, then we can modify the code to the following:
Code: 
Sub Update_XYZ_File()
 
    Set XYZ_file = ThisWorkbook
    Set ABC_file = Workbooks.Open("H:\New folder (2)\New folder\ABC.xlsm") 'provide file path of your required file
 
    Set XYZfile_sheet = XYZ_file.Sheets("Purchase") ' provide the sheet name from your file
    Set ABCfile_sheet = ABC_file.Sheets("Sheet2") ' provide the sheet name from your file
 
    Dim ABClast_row As Integer
    ABClast_row = ABCfile_sheet.Cells(Rows.Count, 5).End(xlUp).Row
    Set ABCsheet_range = ABCfile_sheet.Range("D3:F" & ABClast_row)
 
    Dim i As Integer
    Dim j As Integer
    Dim data_match As Boolean
    Dim XYZlast_row As Integer
    Dim ABC_data As String
    Dim XYZ_data As String
    Dim last_row As Integer
   
    XYZlast_row = XYZfile_sheet.Cells(Rows.Count, 5).End(xlUp).Row
    Set XYZsheet_range = XYZfile_sheet.Range("D3:F" & XYZlast_row)
 
    Application.DisplayAlerts = False
 
    Set missing_data = Nothing
 
    For i = 1 To ABCsheet_range.Rows.Count
        data_match = False
        ABC_data = ""
        ABC_data = JoinRow(ABCsheet_range.Rows(i))
        For j = 1 To XYZsheet_range.Rows.Count
            XYZ_data = ""
            XYZ_data = JoinRow(XYZsheet_range.Rows(j))
            If ABC_data = XYZ_data Then
                data_match = True
                Exit For
            End If
        Next j
        If data_match = False Then
            If missing_data Is Nothing Then
                Set missing_data = ABCsheet_range.Rows(i)
            Else
                Set missing_data = Union(missing_data, ABCsheet_range.Rows(i))
            End If
        End If
    Next i
 
    If missing_data Is Nothing Then
        MsgBox "No missing data found!"
    Else
        missing_data.Copy
        XYZfile_sheet.Cells(XYZlast_row + 1, 4).PasteSpecial Paste:=xlPasteAll
        Application.CutCopyMode = False
    End If
 
    Application.DisplayAlerts = True
    ABC_file.Close SaveChanges:=False

End Sub
Function JoinRow(row_range As Range) As String
    Dim cell As Range
    Dim result As String

    For Each cell In row_range.Cells
        result = result & "," & cell.Value
    Next cell

    JoinRow = result
End Function

I hope I resolved your problem. Let us know your feedback. The workbooks used for this code are attached below.​

Regards,
Seemanto Saha
ExcelDemy
 

Attachments

Last edited:
You must log in or register to reply here.

Online statistics

Members online
0
Guests online
7
Total visitors
7
Totals may include hidden visitors.

Forum statistics

Threads
371
Messages
1,627
Members
705
Latest member
curioso

Share this page

Back
Top