Looping Through auto-filtering values

[Aldaron47]

Hello all,

I have two spreadsheets, one containing user IDs ("Report1") and one containing every information we have about the user with that ID "MASTER_Detail_18.07.2018".

I have created a VBA macro that takes a specific ID ( hardcoded) and uses it as a filter in the "MASTER_Detail_18.07.2018" spreadsheet and then copies two values of the identified user row to the "Report1" spreadsheet which contains the userID.

What I am trying to accomplish is to make the userID used in the filtering to be automatically updated from values on a column. Most specifically I want to loop through column A of "Report1" spreadsheet and for each value of the column to filter the "MASTER_Detail_18.07.2018" spreadsheet, run the code for the copy and then move to the next value of column A of "Report1" spreadsheet.

Following is the code I have managed to create so far

Sub BannerUserMacro()


Windows("Banner Users 08-08-2018.xlsx").Activate
    Sheets("MASTER_Detail_18.07.2018").Select
    ActiveSheet.ListObjects("HRStaff").Range.AutoFilter Field:=6, Criteria1:="MSRA275"
    Range("D3", Range("D1048576").End(xlUp)).Select
    Selection.Copy
    
    Sheets("Report1").Select
    For i = 1 To 2000
        If Cells(i, 1).Value = "MSRA275" And Not IsEmpty(Cells(i, 1).Value) Then
            Cells(i, 4).PasteSpecial Paste:=xlPasteValues
        End If
    Next i
        
    Sheets("MASTER_Detail_18.07.2018").Select
    Range("O3", Range("O1048576").End(xlUp)).Select
    Selection.Copy


    Sheets("Report1").Select
    For i = 1 To 2000
        If Cells(i, 1).Value = "MSRA275" And Not IsEmpty(Cells(i, 1).Value) Then
        Cells(i, 5).PasteSpecial Paste:=xlPasteValues
        End If
    Next i


End Sub

Any help will be greatly appreciated

 

[Fluff]

How about

Sub getID()
   Dim Cl As Range
   Dim Mws As Worksheet, Rws As Worksheet
   
   Set Mws = Workbooks("Banner Users 08-08-2018.xlsx").Sheets("MASTER_Detail_18.07.2018")
   Set Rws = Workbooks("Banner Users 08-08-2018.xlsx").Sheets("Report1")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Mws.Range("F3", Mws.Range("F" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Array(Cl.Offset(, -2).Value, Cl.Offset(, 9).Value)
      Next Cl
      For Each Cl In Rws.Range("A1", Rws.Range("A" & Rows.Count).End(xlUp))
         Cl.Offset(, 3).Resize(, 2).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

This assumes that the IDs are in col F of the Master sheet

 

[Aldaron47]

Thank you very much for posting your answer. I changed just some values because the IDs were located in column G and the offsets to match the columns from where I copied the data and it worked like a charm.

 

[Fluff]

Glad to help & thanks for the feedback