Public Sub SalesSummary()
Const FORMULA_BRAND As String = _
"=INDEX(sales!R1C3:R<sales>C3,MATCH(1,(sales!R1C2:R<sales>C2=RC2)*(sales!R1C5:R<sales>C5=RC5),0))"
Const FORMULA_TYPE As String = _
"=INDEX(sales!R1C4:R<sales>C4,MATCH(1,(sales!R1C2:R<sales>C2=RC2)*(sales!R1C5:R<sales>C5=RC5),0))"
Const FORMULA_SALES As String = _
"=IFERROR(INDEX(sales!R1C6:R<sales>C6,MATCH(1,(sales!R1C2:R<sales>C2=RC2)*(sales!R1C5:R<sales>C5=RC5)*(sales!R1C7:R<sales>C7=R1C),0)),"""")"
Dim wsSumm As Worksheet
Dim wsSales As Worksheet
Dim wsInv As Worksheet
Dim lastsales As Long
Dim lastrow As Long
Dim lastcol As Long
Dim i As Long
Application.ScreenUpdating = False
Set wsSumm = Worksheets("Summary")
Set wsSales = Worksheets("sales")
lastsales = wsSales.Range("A1").End(xlDown).Row
Set wsInv = Worksheets("inventory")
With wsSumm
.UsedRange.ClearContents
wsInv.Range("A1:D7").Copy .Range("A1")
.Columns("C:C").Resize(, 2).Insert Shift:=xlToRight
lastrow = .Range("A1").End(xlDown).Row
.Range("C1:D1").Value = Array("Brand", "Type")
.Range("C2").FormulaR1C1 = Replace(FORMULA_BRAND, "<sales>", lastsales)
.Range("C2").FormulaArray = .Range("C2").Formula
.Range("C2").AutoFill .Range("C2").Resize(lastrow - 1)
.Range("D2").FormulaR1C1 = Replace(FORMULA_TYPE, "<sales>", lastsales)
.Range("D2").FormulaArray = .Range("D2").Formula
.Range("D2").AutoFill .Range("D2").Resize(lastrow - 1)
.Columns("A:E").EntireColumn.AutoFit
wsSales.Range(wsSales.Range("G2"), wsSales.Range("G2").End(xlDown)).Copy
.Range("G2").PasteSpecial Paste:=xlPasteAll
.Range(.Range("$G$2"), .Range("$G$2").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
.Range(.Range("$G$2"), .Range("$G$2").End(xlDown)).Sort Key1:=.Range("G2"), Order1:=xlAscending, Header:=xlNo
.Range(.Range("$G$2"), .Range("$G$2").End(xlDown)).Copy
.Range("G1").PasteSpecial Paste:=xlPasteAll, Transpose:=True
lastcol = .Range("A1").End(xlToRight).Column
.Columns("F:F").Cut
.Columns(lastcol + 1).Insert Shift:=xlToRight
.Range("F2").FormulaR1C1 = Replace(FORMULA_SALES, "<sales>", lastsales)
.Range("F2").FormulaArray = .Range("F2").Formula
.Range("F2").AutoFill Destination:=.Range("F2").Resize(lastrow - 1), Type:=xlFillDefault
.Range("F2").Resize(lastrow - 1).AutoFill Destination:=.Range("F2").Resize(lastrow - 1, lastcol - 6), Type:=xlFillDefault
End With
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub
