VBA - Compare Column on Previous Report With New Report to Find New Entries

I have 4 sheets in total that need to be used.

  • ServerList1
  • ServerList2
  • MachineList1
  • MachineList2

The sheet names with a (1) next to them are the reports from last week and the sheet names with a (2) next to them are the reports from this week.

In each sheet, there are multiple columns which I get rid of so that all that remains is one column with either the Server Name or the Machine Name as the column header

Essentially, I need to compare last weeks report with this weeks report and see what the differences are.

I received the bit bit of code below here:

https://stackoverflow.com/questions/50134391/vba-compare-column-on-previous-report-with-new-report-to-find-new-entries/50134563?noredirect=1#comment87290646_50134563

At first, it appeared to be working, by chance I took a random name and it showed on the one sheet and not the other. But now I see it doesn't work..

Public Sub FindDifferences1()

    Dim firstRange As Range
    Dim secondRange As Range
    Dim myCell As Range

    Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
       
    'Find Removed Wintel Servers
    Set wks1 = ActiveWorkbook.Sheets("Last Week Servers List")
    Set wks2 = ActiveWorkbook.Sheets("This Week Servers List")
    Set wks3 = ActiveWorkbook.Sheets("New Servers")
    
    Set firstRange = wks1.Range("A:A")
    Set secondRange = wks2.Range("A:A")

    For Each myCell In firstRange
        If myCell <> secondRange.Range(myCell.Address) Then
            
            myCell.Copy
            
            wks3.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            wks3.Cells(Rows.Count, 2).End(xlUp).PasteSpecial xlPasteFormats
            
        End If
    Next myCell
    
End Sub

Open in new window


Before I get to this section, I make sure to highlight the cells on the worksheets - each worksheet has a different colour. So that, in theory, with the above code, when it copies, it will copy the cell colour too, indicating which sheet has the difference.
Eitel DagninIT Security AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try
Public Sub FindDifferences1()

    Dim firstRange As Range
    Dim secondRange As Range
    Dim myCell As Range

    Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet
       
    'Find Removed Wintel Servers
    Set wks1 = ActiveWorkbook.Sheets("Last Week Servers List")
    Set wks2 = ActiveWorkbook.Sheets("This Week Servers List")
    Set wks3 = ActiveWorkbook.Sheets("New Servers")
    
    Set firstRange = Range(wks1.Range("A1"), wks1.Range("A" & Rows.Count).End(xlUp))
    Set secondRange = Range(wks2.Range("A1"), wks2.Range("A" & Rows.Count).End(xlUp))

    For Each myCell In secondRange
        If WorksheetFunction.CountIf(firstRange, myCell) = 0 Then
            
            myCell.Copy
            wks3.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            wks3.Cells(Rows.Count, 2).End(xlUp).PasteSpecial xlPasteFormats
            
        End If
    Next myCell
    
End Sub

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eitel DagninIT Security AdministratorAuthor Commented:
Hi Rgonzo1971,

Thank you very much this works perfectly! :)
0
Eitel DagninIT Security AdministratorAuthor Commented:
Did comparison perfectly to show what was on one sheet and now missing on the other.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.