Compare Two Sheets (A and B) and copy & hilight differences to bottom of B

[MarqyMarq]

I have been reading various posts on comparing sheets, and I am pretty close to my solution. Just need a little bit of help on the "compare and copy over to new sheet".

I have two sheets (Summary) and (Tutor Attendance).

Problem 1: I am trying to copy the monthly Summary totals to the Tutoring Attendance for each respective month. If there are NEW entries, then they would be added to bottom, hi-light in Yellow and then sorted by grade (I can do this part).

Problem 2: Each row in Summary has some values (Required and Actual) which will need to be copied over to each respective month in Tutoring Attendance sheet.

Monthly Summary Sheet Shown Below

Tutoring Attendance (Year) Shown below with 2 new students added (in YELLOW)

In reading Fluff's solution in another post, I thought I could modify his code to work with mine. Was not successful bc of the JOIN(Application.Index).

I am looking for another solution as my code does not work to append NEW students to an existing list.

Thank you folks on MrExcel... you do make a difference!

Here is what I have so far: My current code can create NEW Tutoring Attendance list but I cannot append new students to existing student list.

Sub Populate_Tutor_Attendance()

On Error Resume Next

Dim wSTD As Worksheet   '   Defined for STUDENT worksheets
Dim wTA As Worksheet    '   Defined for Tutoring Attendance worksheet
Dim lr As Integer, lrt As Integer   '   Defined to count the number of populated cells in row B of Tutoring Attendance worksheet

Set wTA = Worksheets("Tutoring Attendance")
wTA.Activate    'Activates Tutoring Attendance worksheet

lr = 0          'Sets LR count to "0"
lrt = wTA.Cells(Rows.Count, 2).End(xlUp).Row - 4   'Counts the number of Student Names in row B of Tutoring Attendance worksheet

'Screen does not update from changing sheets
Application.ScreenUpdating = False
   
'Copy contents from Student worksheets to Tutor Attendance sheet
    For Each wSTD In Worksheets
        
        ' Check to see if Students name is present on Tutor Attendance List
            
        ' **** Need code here to compare both sheets and add new students to bottom of list ****
        ' **** NEW code would replace code below:  ****
                
        'IF NOT Present then add names to bottom of list
        If wSTD.Name <> "Instructions" And wSTD.Name <> "Version_Data" And wSTD.Name <> "Summary" And wSTD.Name <> "Tutoring Attendance" And wSTD.Name <> "Master" And wSTD.Name <> "Table Of Contents" Then
          With wSTD
            wSTD.Range("B1").Copy     'Student Name
            wTA.Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            wSTD.Range("F1").Copy     'Grade
            wTA.Range("B65536").End(xlUp).Offset(0, 1).PasteSpecial xlPasteValues
            wSTD.Range("A4").Copy     'Subject
            wTA.Range("B65536").End(xlUp).Offset(0, 2).PasteSpecial xlPasteValues
            
            lr = lr + 1     'Increment lr count by 1
            lrt = lrt + 1
        
        ' If Names are Present then add values to month column
        ' Finds month from Instructions page and and finds column in Tuturoing Attendance  rFind = columncount#
            Dim rFind As Range 'defined to identify column count
            With wTA.Range("E3:U3")
             Set rFind = .Find(What:=Worksheets("Instructions").Range("D4"), LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
             If Not rFind Is Nothing Then
             'MsgBox rFind.Column
             End If
            End With
               
            wSTD.Range("F2").Copy     'Times Required This Month
            wTA.Range("B65536").End(xlUp).Offset(0, rFind.Column - 1).PasteSpecial xlPasteValues    ' Subtracts 2 from previous offset above
            wSTD.Range("F3").Copy     'Times Tutored This Month
            wTA.Range("B65536").End(xlUp).Offset(0, rFind.Column - 2).PasteSpecial xlPasteValues    ' Subtracts 1 from previous offset above
                          
          End With
        End If
    Next wSTD
'
' Sorts the Summary Sheet by Grade(ColumnC)and then by Last Name(ColumnB)
  
'  Range("C7").Select

Application.ScreenUpdating = True
wTA.Activate

'Message box that Summary Sheet Created was successful
MsgBox ("    Tutor Attendance Report Generated!" & vbNewLine & vbNewLine & "Updated With Current Months Data!"), _
       vbInformation, Title:="Generate TUTOR Report"

'lr = wSTD.Cells(Rows.Count, 2).End(xlUp).Row
 
MsgBox lr & " Students Updated!" & vbNewLine & vbNewLine & lrt & " Total students Listed"

End Sub

 

[Fluff]

Duplicate to: Copy Certain Cells to Another Sheet In Correct Row

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.