Option Explicit
Sub Macro3()
Dim lr As Long
Range("A:X,Z:Z,AC:AI").Delete
Range("B:B").ClearContents
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
      Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
      :="-", FieldInfo:=Array(Array(1, 2), Array(2, 2)), TrailingMinusNumbers:=True
Range("A:A").Delete
Rows(1).Insert
Range("A1:C1").Value = Array("DEPT", "AMT", "PLANT")
Range("A:C").HorizontalAlignment = xlCenter
lr = Application.Max(2, Cells(Rows.Count, "A").End(xlUp).Row)
With Range("C2:C" & lr)
  .FormulaR1C1 = "=VLOOKUP(RC1,TABLE!R1C1:R133C2,2,FALSE)"
  '.Value = .Value 'include this if you want to convert formulae results into plain values.
End With
AddPivot    'adds pivot table.
End Sub


Sub AddPivot()
With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
      Sheets("REPORT").Cells(1).CurrentRegion.Resize(, 3)).CreatePivotTable(TableDestination:= _
      Sheets("REPORT").Range("E1"))
  .RowAxisLayout xlTabularRow
  .PivotFields("PLANT").Orientation = xlRowField
  .AddDataField .PivotFields("AMT"), "Sum of AMT", xlSum
End With
End Sub