Automating MATCH result data copying

[jjelliott]

I am going preface this with a warning that I am clueless.

First an overview of what I am trying to accomplish.

This is in Excel 365

I have a source sheet that is generated by an application once/week.  The report contains both data that has changed since last run and new data. The file name changes each time that it is run, the file is then deleted so no formulas can be stored in it.  I do maintain a personal.xlsb file with vba.

Source and destination sheets are formatted the same. 

Data in the source will have new rows that should be appended to the destination 

Data in the source will have existing data that will have changed and should overwrite the destination sheet.  Column A is the key for matching, this cell does not change.

At the moment, I am using   =MATCH(A:A,report1652227282759.xlsx!$A:$A,0)  in the last column on every row of the destination sheet to locate the matching (changed) rows on the source sheet.  The then filter out the #N/A cells and copy the entire contents of the source to the destination.

My goal is to automate this. 

1) Modify the formula so that the report name is whatever the source file is when I am running it.  (report####.xls)  dim ws as worksheet    set ws=thisworkbook.activesheet ?

2) Copy the formula to every row on the destination each time I run the report without manually selecting all rows and using CTRL+D.  

3) Filter out #N/A on the Destination (Daily cases.XLS)

4) copy the contents of the source XLS to the destination XLS.

I hope this make sense to you much smarter than me Excel folks.

I am wide open to scraping the entire approach if there is a better way.

File samples are attached.

[#2]

@jjelliott 

In the attached, you will find two buttons (look at the range O1:S1) called "Match Cases With Report File" and "Show All Data".

If you click the button called "Match Cases With Report File", it will open a file picker dialogbox for you, please browse and select your report file and click open to continue. The code will then do the rest job and filter column M to show you the matching row# from the report file.

To see all the data again, just click on the Show All Data button.

Code for Module1:

Option Explicit

Sub MatchCaseNumbersWithReportFile()
Dim dwb             As Workbook
Dim swb             As Workbook
Dim dws             As Worksheet
Dim sws             As Worksheet
Dim srcRepotPath    As String
Dim lr              As Long
Dim dict            As Object
Dim x               As Variant
Dim y               As Variant
Dim i               As Long
Dim j               As Long
Dim arrOut()        As Variant

Application.ScreenUpdating = False

Set dwb = ThisWorkbook
Set dws = dwb.Worksheets("Cases")

Set dict = CreateObject("Scripting.Dictionary")

With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select a Report File to Match CaseNumbers"
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xls*"
    
    If .Show = -1 Then
        srcRepotPath = .SelectedItems(1)
    Else
        MsgBox "You didn't select any Report File!", vbCritical
        Exit Sub
    End If
End With

Set swb = Workbooks.Open(srcRepotPath, False)
Set sws = swb.Worksheets(1)

x = sws.Range("A1").CurrentRegion.Value

For i = 2 To UBound(x, 1)
    dict.Item(x(i, 1)) = i
Next i

swb.Close False

If dws.FilterMode Then dws.ShowAllData

y = dws.Range("A1").CurrentRegion.Value

ReDim arrOut(1 To UBound(y, 1) - 1, 1 To 1)

For i = 2 To UBound(y, 1)
    j = j + 1
    If dict.exists(y(i, 1)) Then
        arrOut(j, 1) = dict(y(i, 1))
    Else
        arrOut(j, 1) = ""
    End If
Next i

dws.Range("M2").Resize(j, 1).Value = arrOut

With dws.Range("A1:M1")
    .AutoFilter field:=13, Criteria1:="<>"
End With
Application.ScreenUpdating = True
End Sub

Sub ShowAllData()
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
End Sub

[#3]

@Subodh_Tiwari_sktneer

Thank you. That is a nice way to save me from manually copying the code. My hope was to nat have to save the source file but that is a minor issue. Unfortunately, it does not accomplish my goal of replacing the rows in the "daily cases" worksheet with the matching rows in the "reportxxxxx" worksheet and appending the new any non-matching (therefore, new) rows to the end of the "Daily Cases' sheet.