VBA to Vlookup Concatenation and Paste as Values

[nirvehex]

I have a Table, called "Table2" on "Sheet1". I also have a PivotTable, called "PivotTable2" on "Pivot" tab.

Headers that will be important in Table2 are "Location" and "IDNum" and "Service" and "Size".

I'm trying to write some VBA that does this:

1. Filters "Service" column for values of "CS", "DS", "LR", "M", "R", "RC". Sometimes all of these will be present, sometimes only some of them, so it shouldn't error out if it doesn't find some of these values, should just move on to looking for the next value.

2. In the "Size" column for only these filtered rows, Vlookup the concatenation of the corresponding values on same row in "Location"-"IDNum" against "PivotTable2" with the TableArray being columns "Y:AB" on the "Pivot" tab, returning the 4th column,"AB".

3. Paste these Vlookups as Values.

I feel like this should be easy enough, but I can't quite get it with the Macro recorder. Any ideas?

Thanks!

 

[pbornemeier]

Option Explicit

Sub FilterIT()
    Dim rngCell As Range
    Dim sKey As String
    With ActiveSheet.ListObjects("Table2")
        .Range.AutoFilter Field:=3, Criteria1:= _
            Array("CS", "DS", "LR", "M", "R", "RC"), Operator:=xlFilterValues
        If Application.WorksheetFunction.Subtotal(3, .Range.Columns(3)) > 1 Then
            'There are some filtered rows visible
            For Each rngCell In .DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible).Cells
                sKey = rngCell.Value & "-" & rngCell.Offset(0, 1).Value
                rngCell.Offset(0, 3).Value = _
                    Application.WorksheetFunction.VLookup(sKey, Sheets("Pivot").Range("Y:AB"), 4, False)
            Next
            If .Range.AutoFilter Then .Range.AutoFilter
        End If
    End With
End Sub