Excel Forum

Code: Select all

Sub VlookupToPivot()
   Application.ScreenUpdating = False
     Dim lastrow As Long
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    Sheets(1).Select
    Range("H2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC1,INDIRECT(""'""&R1C&""'!$H$2:$I$5000""),2,FALSE),"""")"
    Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:S2"), Type:=xlFillDefault
    Range("H2:S2").Select
    Selection.AutoFill Destination:=Range("H2:S" & lastrow)
    Range("H2").Select
  Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("H1").Select
End Sub

Code: Select all

Sub VlookupToPivot()
    Dim lastrow As Long
    Application.ScreenUpdating = False
    Sheets(1).Select
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("h2:s" & lastrow).FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC1,INDIRECT(""'""&R1C&""'!$H$2:$I$5000""),2,FALSE),"""")"
    Range("h2:s" & lastrow).Value = Range("h2:s" & lastrow).Value
    Range("h1").Select
    Application.ScreenUpdating = True
End Sub