Add rows to worksheet if value does not exist in another sheet

[WarrenX148]

Good day everyone,

I have a fairly complex worksheet going currently that I have been able to maintain without VBA, but it seems that what I am looking to do at this point requires it.

On the "master sheet" I have a list of employees, their SSN (which is what I use for most queries) and several other pieces of data that we have on those employees.

Each month, I receive a report from another office that I import into a new tab in the sheet. It contains the employee names, SSNs, and the number of hours they have worked for the employer. It does not contain the other data that is exclusive to us.

I have the master sheet set up to VLOOKUP based on the employee SSN and provide me their hours worked. There are some other functions that also happen that are less relevant. However, if I don't have the employee and their SSN on the master sheet, I don't get anything, so it is easy to miss someone who is not being tracked.

I want to add a person's name and SSN to the master sheet if they do not yet exist on it so I am sure I am capturing everyone's data. Since there are so many tabs (many years worth), I'd also need to be able to tell it which tab I want to look at to do this.

I've found snippets of code that will add rows of missing data within the same worksheet, snippets that allow dialog boxes for inputs, and the like, but I am not very VBA savvy and am unable to put it all together into a functional macro that will accomplish the whole goal.

Any help is greatly appreciated!

 

[WarrenX148]

Update: I found another post on here that seems to be able to do 90% of what I'm looking for, but it errors with a type mismatch. The code works when I try to do other columns, but when I'm doing SSNs it pukes. It seems to be because the SSNs have dashes in them, because if I take the dashes out, the code works again.

Sub addssn()
   Dim Cl As Range
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets(35)
      For Each Cl In .Range("D2", .Range("D" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Empty
      Next Cl
   End With
   With Sheets(1)
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If Dic.Exists(Cl.Value) Then
            Cl.Offset(, 1).Value = Cl.Offset(, 1).Value + 1
            Dic.Remove Cl.Value
         End If
      Next Cl
      If Dic.Count > 0 Then
         With .Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Dic.Count)
            .Value = Application.Transpose(Dic.Keys)
            .Offset(, 1).Value = 1
         End With
      End If
   End With
End Sub

 

[WarrenX148]

I seem to have found a workaround. The code adds a 1 in the column next to the column it's adding the SSNs. I created an empty column and hid it and the code works now.

 

[Alex Blakenburg]

Thank you for providing your solution. I suggest you actually Mark your post 2 as the solution. I am sure others will find it helpful.

PS: I see no reason why a dash would cause the code to fail and it depends on what you actually meant. Your expression indicates the code crashed.
I can see there would be a mismatch (item not found) if one side has real dashes and the other side either doesn't have dashes or the dashes are a custom format and not real dashes but it should not cause the code to crash.