Results 1 to 3 of 3

Thread: Macro to pull data ranges and comapre them

  1. #1
    Junior Member
    Join Date
    Mar 2014
    Posts
    2
    Rep Power
    0

    Macro to pull data ranges and comapre them

    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

    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
    Last edited by bakerman; 03-26-2014 at 04:04 AM. Reason: Code tags added for readability

  2. #2
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    12
    Some remarks.
    Code:
    lrow = ActiveSheet.Range("a1").End(xlUp).Row
    lRow always result in 1 since A1 is top row.
    Code:
    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.
    Last edited by bakerman; 03-26-2014 at 10:25 AM.

  3. #3
    Junior Member
    Join Date
    Mar 2014
    Posts
    2
    Rep Power
    0
    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.

Similar Threads

  1. Replies: 11
    Last Post: 10-13-2013, 10:53 PM
  2. Replies: 2
    Last Post: 09-30-2013, 03:40 PM
  3. Replies: 2
    Last Post: 04-26-2013, 04:59 PM
  4. IE Automated Login/Table Pull
    By mrmmickle1 in forum Hire A Developer
    Replies: 7
    Last Post: 04-04-2013, 04:58 PM
  5. Replies: 3
    Last Post: 05-14-2012, 11:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •