VBA to Compare sheets, and insert missing data, while skipping over existing items that may be in master list

[torrino1]

I am attempting to compare lists of data and insert missing rows. The below code is intended to do so, however, if it encounters an items in the Account Master List that is not on the Execute billing list it will insert rows for everything that comes after that row of data. Any help is appreciated.

    Dim Cnt As Long
    Dim Sht1 As Worksheet
    Dim Sht2 As Worksheet
    
    Set Sht1 = Sheets("Execute Billing")
    Set Sht2 = Sheets("Account Master File")
    
  For Cnt = 2 To Sht1.Range("A" & Rows.Count).End(xlUp).Row

        If Sht1.Range("A" & Cnt).Value <> Sht2.Range("A" & Cnt) Then
           Sht2.Rows(Cnt).Insert
           Sht2.Range("A" & Cnt).Value = Sht1.Range("A" & Cnt).Value
           Selection.NumberFormat = "000000000000000"
           Sht2.Range("B" & Cnt).Value = Sht1.Range("C" & Cnt).Value
           Sht2.Range("C" & Cnt).Value = "NEW"
           Sht2.Range("D" & Cnt).FormulaR1C1 = "=SUM(RC[-1]*5+18)"
           Sht2.Range("E" & Cnt).FormulaR1C1 = "=RC[4]&TEXT(RC[-4],""000000000000000"")&RC[5]"
           Sht2.Range("F" & Cnt).FormulaR1C1 = "=SUM(RC[-2]*100)"
           Sht2.Range("H" & Cnt).FormulaR1C1 = "=RC[-3]&TEXT(RC[-2],""0000000000000"")"
           Sht2.Range("I" & Cnt).Value = "'001"
           Sht2.Range("J" & Cnt).Value = "'0125"
End If
Next Cnt

 

[Fluff]

Does the "Execute Billing" sheet contain a complete list of possible values?

 

[torrino1]

Fluff,
Execute billing contains the new month data that need be queried to add any missing values to the account master list.
There are continual adds to the master list so am hoping to compare the new month data in the Execute billing tab for anything missing from the account master list.

Would Is Not operator be an option to allow insertion only If items in Execute are not in master?
Appreciate your willingness to assist a novice VBA guy.

 

[Fluff]

How about

Sub UpdateMaster()
   Dim Cl As Range
   Dim Dic As Object
   Dim Rng As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets("Execute Billing")
   Set Ws2 = Sheets("Account Master File")
   Set Dic = CreateObject("scripting.dictionary")
   
   For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
      Dic(Cl.Value) = Cl
   Next Cl
   For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
      If Not Dic.exists(Cl.Value) Then
         If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
      End If
   Next Cl
   If Not Rng Is Nothing Then
      With Ws2.Range("A" & Rows.Count).End(xlUp)
         Rng.Copy .Offset(1)
         Rng.Offset(, 2).Copy .Offset(1, 1)
         .Offset(1, 2).Resize(Rng.Count).Value = "NEW"
         .Offset(1, 3).Resize(Rng.Count).FormulaR1C1 = "=SUM(RC[-1]*5+18)"
         .Offset(1, 4).Resize(Rng.Count).FormulaR1C1 = "=RC[4]&TEXT(RC[-4],""000000000000000"")&RC[5]"
         .Offset(1, 5).Resize(Rng.Count).FormulaR1C1 = "=SUM(RC[-2]*100)"
         .Offset(1, 7).Resize(Rng.Count).FormulaR1C1 = "=RC[-3]&TEXT(RC[-2],""0000000000000"")"
         .Offset(1, 8).Resize(Rng.Count).Value = "'001"
         .Offset(1, 9).Resize(Rng.Count).Value = "'0125"
      End With
   End If
   
End Sub

This will add the new values to the bottom of the sheet

 

[torrino1]

That seems to have solved my conundrum. Thank you ever so much for your help

 

[Fluff]

Glad to help & thanks for the feedback