Import from second workbook plus some magic

[keiranwyllie]

Good afternoon Guru's

I'm racking my brain with the best way forward on this. I have two spreadsheets (similar examples attached), one holding source data and the second is used for reporting. The source data comes from another source and therefore doesn't contain comments (column G). The report imports the source data (done weekly) and then tracks specific issues with each job by then adding comments.

The source data is updated externally on a weekly basis and therefore will contain new jobs, or will have removed jobs that have been closed during the previous week (I've explained this veru basically in the flow.jpg attached). The report workbook is something I control that, at this point in time, contains a lot more than the example attached. What I want to be able to do is import the newest source data workbook into the report workbook (to the 'Source' tab). I need to then compare the new source data with the data in the 'Job Comments' tab to both remove closed jobs and add new jobs (into the 'Job Comments' tab) while at the same time retaining any comments in column G for jobs that are still open.

All I can think of is opening each workbook in various orders and copying worksheets back and forth multiple times and using filters to delete certain rows that don't match but my head is hurting from even contemplating that. There has to be an easier way...right. Well hopefully. I'd love just a point in any direction that could possible help. TIA

The files are located here https://github.com/keiranwyllie/excel

 

[keiranwyllie]

Well I continued to work away at this and I've had success for anyone that may wish to do anything similar. The files are still in the same location if you want to take a look. See below for the code I used.
I feel there is a better way to delete the column in the last part of the code because I'd prefer to only copy a range rather than the entire row however this still gives me the outcome I'm after.

Sub CheckValues()Dim CurrVal As String
Dim currcell As String
Dim ChkVal As Variant
Dim lr As Long
Dim chklr As Long
Dim rngtochk As Range
Dim i As Long
Dim x As Long
Dim fromws As Worksheet
Dim chktows As Worksheet
Dim LastRow As Long
Dim h As Long, j As Long


Worksheets("Job Comments").Activate


Set fromws = Sheets("Job Comments")
lr = fromws.Cells(Rows.Count, "B").End(xlUp).Row




For i = lr To 2 Step -1
    currcell = Cells(i, 2).Value
    For x = 1 To 1
        Select Case x
            Case 1
                Set chktows = Sheets("Source")
                chklr = chktows.Cells(Rows.Count, "B").End(xlUp).Row
                Set rngtochk = chktows.Range("B2:B" & chklr)
        End Select
        If Application.WorksheetFunction.CountIf(rngtochk, currcell) = 0 Then
            fromws.Rows(i).Delete
        End If
    Next
Next


   'Find the last used row in a Column: column A in this example
   With Worksheets("Source")
      LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   End With


   'MsgBox (LastRow)
   'first row number where you need to paste values in Sheet1'
   With Worksheets("Job Comments")
      j = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
   End With


   For h = 1 To LastRow
       With Worksheets("Source")
           If .Cells(h, 9).Value = "Unique" Then
               .Rows(h).Copy Destination:=Worksheets("Job Comments").Range("A" & j)
               j = j + 1
           End If
       End With
   Next h
   
   Worksheets("Job Comments").Columns(9).ClearContents
   
End Sub

My next attempt will be to grab the new data and add it to an email.