Compare Two Sheets (A and B): Some data NOT copying to Sheet B

[MarqyMarq]

I've have come close to solving my original dilemma: Compare Two Sheets (A and B) and copy & hilight differences to bottom of B
NOTE: I did try to use XLS2BB add-in and it locks up my EXCEL program.

In breaking down my original problem, I have come to this road block: some data is not copying from the first sheet to second sheet when I run compare code.

I am comparing row A in Summary sheet against row B in Tutoring sheet and copying differences (new data) to (B). The problem is when I run the code, some of the values from row A Summary are repeated in Tutoring (B) while some data is not copied at all.

The names in Yellow should be the ones added, but when I run the code, some data is copied over while other cells are not.

Summary sheet

Tutoring sheet View attachment 43932

Here's the code I have so far.

Private Sub Find_Match_Summary()

On Error Resume Next

Dim ws1 As Worksheet, ws2 As Worksheet
Dim i As Integer, j As Integer, a As Integer, b As Integer

Set ws1 = ActiveWorkbook.Sheets("Summary")              ' Column A  relates to a = row 4  and i = 4
    a = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row      ' Last filled cell in Column A of Summary
    i = 4                                               ' Beginning row of compare for Summary

Set ws2 = ActiveWorkbook.Sheets("Tutoring Attendance")  ' Column B  relates to b  = row 5  and j =5
    b = ws2.Cells(ws1.Rows.Count, 2).End(xlUp).Row      ' Last filled cell in Column B of Tutoring
    j = 5                                               ' Beginning row of compare for Tutoring

For i = 4 To a
    
    ws1.Activate
    ws1.Range(i, 1).Select                              ' Select first cell to compare
    If Trim(ws1.Cells(i, 1).Value2) = Trim(ws2.Cells(j, 2).Value2) Then
        MsgBox "Cells are True for = " & ws2.Cells(j, 2).Value2
            
    Else
        ws1.Range("A" & i, "B" & i).Copy                    ' Copy the two cells of data
        ws2.Activate                                        ' Tutoring Sheet
        b = ws2.Cells(ws1.Rows.Count, 2).End(xlUp).Row      ' Last filled cell in Column B of Tutoring
        ws2.Cells(b + 1, 2).Select                          ' First empty cell in Column B of Tutoring
        ws2.Range("B50").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues ' Paste VALUES only to new rows
        MsgBox "Verify " & ws2.Cells(j, 2).Value2 & " was copied over"    ' Used to verify correct Value was copied over
    End If
    
Next i

Application.CutCopyMode = False

ws1.Activate  ' Tutoring Attendance sheet activated

MsgBox "Find_Match_Summary - Dun!"

End Sub

Please provide me some assistance with my code or even some different method to solve my problem. I am soooo close!

Thanks folks!

 

[MarqyMarq]

Here is the pic which shows the current FAILED state of the VBA code.

I am stumped as to why it copies the same names on the last two, but not the first one (Delta, Aubrey).

Thanks again folks!

 

[MarqyMarq]

Hello Mr Excel, wondering if anyone has a solution to this problem of mine. Not wanting to violate the Ground rules (#12), so I am asking for some help again on this issue.

Please provide me some assistance with my code or even some different method to solve my problem. I am soooo close!

Thanks EXCEL folks!

 

[MarqyMarq]

Here's an update to my original problem. I wanted to use an array solution, but I don't know how to use arrays, so I found a different solution: a "helper column" added to my Summary tab (first worksheet).

In a vacant column (Blue Arrow), I added the following code to my spreadsheet:

=IF(A4<>"",IF(ISNA(VLOOKUP(A4,'Tutoring Attendance'!$B$5:$B$44,1,FALSE)),"No","Yes"),"")

Then I run my VBA code to search for the value of "NO" in that column and then copy the "names" to the bottom of the other sheet. This works as well.

It's a 95% solution so far. If anyone can help with the last part, I'd appreciate it.
- The NEW names in the second worksheet (Tutoring Attendance) should show up as YELLOW background, but the copy/paste function is copying the range. I cant figure out how to copy only the "values'. I tried PasteSpecial but get an error.
- Also can't figure out how to copy the "Range of A & B" over to the second sheet also. When I tried, using the CELLS rather than RANGE, I got errors I couldn't figure out.

Sub CopySummaryNames_TANames()
    Dim wSum As Worksheet   '   Defined for Summary worksheet
    Dim wTA As Worksheet    '   Defined for Tutoring Attendance worksheet
    Dim IRg As Range, xCell As Range, ARg As Range, MReqRg As Range, MActRg As Range
    Dim lr As Integer, lrt As Integer   '   Defined to count the number of populated cells in row B of Tutoring Attendance worksheet
    
        
    Dim ICount As Long
    Dim K As Long
    
    Set wSum = Worksheets("Summary")
    Set wTA = Worksheets("Tutoring Attendance")
    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
    
    ICount = wSum.Cells(Rows.Count, 1).End(xlUp).Row   ' Identifies the last row of data in Summary sheet
            
    Set IRg = wSum.Range("I4:I" & ICount)   ' *** Helper Column *** Sets the range in row I, which is the criteria column equals "No"
    Set ARg = wSum.Range("A4:A" & ICount)   ' Sets the range in row A, which is the NAME column
    
    'On Error Resume Next
    
    Application.ScreenUpdating = False
    
        For K = 1 To IRg.Count
            If CStr(IRg(K).Value) = "No" Then
                '
                ' *** Color codes the ADDED cells to YELLOW - NOT Working
                wTA.Range("B" & Rows.Count).End(xlUp).Offset(1).Interior.Color = vbYellow
                ' Code below copies cell range including formatting which overwrites the YELLOW color
                                
                ' ***Copies names to Tuturing Attendance - WORKS, but need it to only copy "values"
                ARg(K).Cells.Copy Destination:=wTA.Range("B" & Rows.Count).End(xlUp).Offset(1)
                
                lr = lr + 1     'Increment lr count by 1 when a Student is added to Tutoring Attendance
                lrt = lrt + 1
                
            End If
        
            ' ***Insert Monthly Values in Proper Month Column ***  TESTING 12 Aug 2021 *** SEE Below Code
            ' If Names are Present then add values to month column
            ' Finds month from Instructions page and and finds column in Tuturoing Attendance  rFind = columncount#
        
        Next
    
    Application.ScreenUpdating = True
    
MsgBox lr & " Students Updated!" & vbNewLine & vbNewLine & lrt & " Total students Listed"

End Sub

Thanks guys for your help. I just wanted to update my post from a couple of weeks ago.

 

[Alex Blakenburg]

Either of these will work:-

for Options 1 & 2 replace this with what is in the option:

                ' ***Copies names to Tuturing Attendance - WORKS, but need it to only copy "values"
                ARg(K).Cells.Copy Destination:=wTA.Range("B" & Rows.Count).End(xlUp).Offset(1)

Option 1) Pastespecial

                ARg(K).Cells.Copy
                wTA.Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

Options 2) Assign Value (faster - preferred if you only want values)

wTA.Range("B" & Rows.Count).End(xlUp).Offset(1).Value = ARg(K).Cells.Value

Given where you were at with a full copy, you could have simply changed the order of the what you were doing.
- Copy the name across 1st
then
- highlight the cell in yellow
(you would have had to remove the .offset(1) - since you now had one more filled cell, having already added the name)

 

[MarqyMarq]

Hi Alex, Thanks for the help! I did use the second option since I have been seeing it has a better outcome. It did work!

In your response you stated:

Given where you were at with a full copy, you could have simply changed the order of the what you were doing.
- Copy the name across 1st
then
- highlight the cell in yellow

I tried that before, and it wasn't working out, so I had to reverse the order. I did see that I should have removed the .offset(1)... It does work as you suggested!!!

Thanks a bunch.

One more tweak... In the image above (post #4), you recognize column "D" is blank. I have been trying to get a date in the NEW names as they are initially added.
The idea is to get the date from cell "D3" and place it in the respective column ...only when it is first added into the Summary Table, so I think I have it in the right place.
However, I get an "Invalid procedure call or argument" error at the PASTE command.

For K = 1 To IRg.Count
            If CStr(IRg(K).Value) = "No" Then
                '
                ' ***Copies names to Tuturing Attendance - WORKS great!  Thanks Alex Blakenburg (Mr Excel Member)
                ARg(K).Cells.Copy
                wTA.Range("B" & Rows.Count).End(xlUp).Offset(1).Value = ARg(K).Cells.Value
                
                ' ***Copies Date from Cell D3 to respective NEW student name - Errors out with "Invalid procedure call or argument"
                wTA.Cells("D3").Copy
                wTA.Range("D" & K).Paste
                                                
                ' *** Color codes the ADDED cells to YELLOW - WORKS great!  Thanks Alex Blakenburg (Mr Excel Member)
                wTA.Range("B" & Rows.Count).End(xlUp).Interior.Color = vbYellow
                ' Code below copies cell range including formatting which overwrites the YELLOW color
                
                lr = lr + 1     'Increment lr count by 1 when a Student is added to Tutoring Attendance
                lrt = lrt + 1
                
           End If
 Next

I tried different things, but I cant get it to paste the date to the Students row.

 

[Alex Blakenburg]

The code is having to look up the last row on the Tutorial sheet multiple times.
If you make these changes it should solve your date issue and address looking up the last row each time.

1) At the top add this Dim statement

Dim lastRowTut As Long

2) Replace your For loop and at the same time add the row I have before the for loop.

    lastRowTut = wTA.Range("B" & Rows.Count).End(xlUp).Row
    
    For K = 1 To IRg.Count
         If CStr(IRg(K).Value) = "No" Then
            lastRowTut = lastRowTut + 1
             '
             ' ***Copies names to Tuturing Attendance - WORKS great!  Thanks Alex Blakenburg (Mr Excel Member)
             ARg(K).Cells.Copy
             wTA.Range("B" & lastRowTut).Value = ARg(K).Cells.Value
             
             ' ***Copies Date from Cell D3 to respective NEW student name - Errors out with "Invalid procedure call or argument"
             wTA.Range("D3").Copy Destination:=wTA.Range("D" & lastRowTut)
                                                     
             ' *** Color codes the ADDED cells to YELLOW - WORKS great!  Thanks Alex Blakenburg (Mr Excel Member)
             wTA.Range("B" & lastRowTut).Interior.Color = vbYellow
             ' Code below copies cell range including formatting which overwrites the YELLOW color
             
             lr = lr + 1     'Increment lr count by 1 when a Student is added to Tutoring Attendance
             lrt = lrt + 1
             
        End If
     Next

Note: The above follows on from what you had and assumes you have a date in D3 on the Tutorial sheet.
If you just wanted today's date, you could also have replaced the copy paste with this:-

wTA.Range("D" & lastRowTut) = Date

 

[MarqyMarq]

Hi Alex, I know it's late for you!!! Your code worked as designed. I liked how you streamlined the code.

However, when I protect the sheet so no one can change the template, I get an error code. The details are in the other problem you and I have been working.

Good night my Friend!

 

[Alex Blakenburg]

I have replied on the other thread. Thank you and Good night.