Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA code to VLOOKUP and display missing values in new workbook?

0
I was looking for some VBA code to help me with the following task:
  • I have 2 workbooks with similar data. I want to vlookup all the values (each row) from WorkBook1 across all the values in WorkBook2 (all rows). I then want to write all the missing values to a new worksheet / workbook. (Im working with .xlsx)

For example,

WorkBook1

Sam

Danny

Rachel

Patricia

WorkBook 2

Sam

Rachel

Patricia

John

(The code should search for Sam across all rows of workbook 2. If it is found, it should move to the next row and look for 'Danny'across all rows. In this example, danny doesnt exist in WorkBook 2, and hence the code should write this entry to a new worksheet / workbook).

Im a beginner to VBA

Thank you!

Answer
Discuss

Answers

0

Hi There 

You might like to look at 

https://www.teachexcel.com/free-excel-macros/2077/automatically-copy-new-data-to-another-worksheet

which almost does what you want. 

Discuss
0
Sub Main()
    Dim ws1 As Worksheet, r1 As Range, f1 As Range
    Dim ws2 As Worksheet, r2 As Range, f2 As Range
    Dim ws3 As Worksheet, a, i As Long

    'ws1 and ws2 workbooks are expected to be open
    Set ws1 = Workbooks("FindMatch1.xlsx").Worksheets(1)
    Set ws2 = Workbooks("FindMatch2.xlsx").Worksheets(1)

    Set r1 = ws1.Range("A2", ws1.Cells(Rows.Count, "A").End(xlUp))
    Set r2 = ws2.Range("A2", ws2.Cells(Rows.Count, "A").End(xlUp))
    ReDim a(1 To 1)
    For Each f1 In r1
        Set f2 = r2.Find(f1)
        If f2 Is Nothing Then
            i = i + 1
            ReDim Preserve a(1 To i)
            a(i) = f1
        End If
    Next f1

    If a(1) = "" Then Exit Sub
    Set ws3 = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    ws3.Range("A2").Resize(UBound(a)) = WorksheetFunction.Transpose(a)
End Sub
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login