Your LOOKUP formula is not necessarily a good choice, it's a legacy function and needs the lookup table to be in the right order. I see it's not in
Macro3, however that formula isn't quite right vis-a-vis the absolute/relative references. The attached contains the right formula in
Macro3.
Macro3 has been streamlined. It also calls the
AddPivot macro at the end.
For a
REPORT sheet that's already got the data that your original
Macro3 left there, there's a button to add a pivot table. It's a very, very basic pivot table.
Code:
Code:
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