Macro to pull data ranges and comapre them

[aababneh86]

Hi everyone, I am trying to write a macro in which I get prompted to browse for 2 files and then, to extract specific (fixed) ranges and them compare them for differences. I know it should be easy but it has been a while since I used VBA. The code I have so far is the following, but it is creating many duplicates. Please amend or kindly write me a new code

Sub HowardC()
Dim AllCells As Range
Dim cell As Range, Rng As Range
Dim lrow As Long, Rlrow As Long
Dim Myval As Integer
Dim Item As Variant
Dim wb As Workbook
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = "C:\" & ThisWorkbook.Path            ' Default path
        .FilterIndex = 3
        .Title = "Please Select a File"
        .ButtonName = "Open"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count = 0 Then Exit Sub   ' User clicked cancel
        Set wb = Workbooks.Open(FileName:=.SelectedItems(1))
    End With
    lrow = ActiveSheet.Range("a1").End(xlUp).Row
    Set AllCells = Range("A1:A" & lrow)
        
    For Each Item In AllCells
    Range("D1:E1").Select     '' E-H
        Selection.AutoFilter
        With Selection
            .AutoFilter Field:=2, Criteria1:=Item '' this set the filtered data for the value
        End With
            Set Rng = ActiveSheet.AutoFilter.Range
                Rlrow = ThisWorkbook.Sheets("TBTXT2").Range("C65536").End(xlUp).Row + 1
                Rng.Offset(1, 1).Resize(Rng.Rows.Count - 1, 4).Copy Destination:=ThisWorkbook.Sheets("TBTXT2").Cells(Rlrow, 2)
    Next Item
    Selection.AutoFilter
ActiveWorkbook.Close False
End Sub

Thanks a lot

[bakerman]

Some remarks.

lrow = ActiveSheet.Range("a1").End(xlUp).Row

lRow always result in 1 since A1 is top row.

Set AllCells = Range("A1:A" & lrow)

AllCells always result in 1 cell value being A1 value.
Autofilter will therefore filter on 1 value only and the For...Next loop has no added value.
Avoid using Select and Selection in code because this is really unnecessary. Instead use With...End With statement.

[aababneh86]

Many thanks for your reply. Are you able to write up a quick code to entirly replace this code, as I think I am stuck here. I just need to pull specific data ranges from 2 files that I will browse for, and then I will use the excel compare features. so I just need the code to broswse and pull specific (fixed) data ranges from the browsed for files. Your help is much appreciated.