How to Create a macro to copy over multiple cells in a certain row based on a cells value

here is some theory and a little code to get you started...... not sure if you have started anything yet or how familiar you are with vba so here goes nothing

put all the data into variables (standard procedure)
Loop through the table holding the data you want to transfer first
inside this loop, loop through the other table and check if the record exists (or forget this loop and just remove duplicates later up to you)
if your clear to add the record check the column you needed to check if its resignation put resignation, if not put term...
finished....

   dim Rng1 as range
   dim Rng2 as range

   dim tArr1 as variant
   dim tArr2 as variant

   dim i as long, j as Long, k as integer   'Use long when counting rows and not integer

   dim IsOK as boolean
   
   set Rng1 = Sheet1.usedrange 'use your sheets name (in the VBE window you will see 2 names use the one not in ().....Sheet1(NameOfSheet)
   set Rng2 = Sheet2.Usedrange  '<--this is the table your transferring data too, the one above is the one your getting data from


  EDIT*******
   tarr1 = rng1.value
   tarr2 = rng2.value
   'we use the arrays to loop because they are faster, there are other ways but this is good practice....

   for i = lbound(tarr1,1) to ubound(tarr1,1)   'loop sheet1 rows
      for j = lbound(tarr2,1) to ubound(tarr2,1)   'loop sheet2 rows

           IsOK = True
           for k = lbound(tarr1,2) to ubound(tarr1,2)   'loop sheet1 columns (assuming the tables are the same size on both tables)
               if not tarr1(i,k) = tarr2(j,k) then 
                   IsOK = false
                   exit for
               end if
           next k

           If IsOK then
               'here you check if the column your watching is term or resign etc etc
                if tarr1(i,colnumber) = "ThisText" then   '<---change column number and ThisText to the correct values
                    Tarr1(i,colnumber) = "Resign"
                Else
                     tarr1(i,colnumber) = "Quit"
                end if

                'cheating here better to use another array not add values to cells directly but im running out of time sorry
                 set rng1 = sheet2.usedrange
                 set rng1 = sheet2.rows(rng1.row + rng1.rows.count - 1).offset(1,0) 'might need to double check this, should return range 1 row under your table
                 for k = lbound(tarr1,2) to ubound(tarr1,2)
                      rng1.cells(k) = tarr1(j,k)
                 next k
           End if
      next j
   next i

Sorry about the formatting, this will give you something to play with anyway, its using arrays and rngs so you can see how they work..

Thanks
Paul S.