I needs help on with filter different brand of item, as current VBA code was all using range cell to filter into different column. This make a lot of code needed to amend when there is a new product. Is there a way to filter each brand with unique SKU like (BrandA, BrandB, BrandC)? Thanks
Before
After
Before
After
VBA Code:
Sub Report()
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Main"
Dim ResultCell As Range
Dim Sheet As Worksheet
Set Sheet = ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))
Sheet.Name = "Report"
Sheets("Main").Range("$A$4:$A$21").Copy
Sheets("Report").Activate
Range("A4").Select
ActiveSheet.Paste
Sheets("Main").Range("$A$22:$A$35").Copy
Sheets("Report").Activate
Range("D4").Select
ActiveSheet.Paste
Sheets("Main").Range("$A$36:$A$45").Copy
Sheets("Report").Activate
Range("G4").Select
ActiveSheet.Paste
Dim LookupValueCell As Range
Dim LookupVector As Range
Dim ResultVector As Range
Set ResultCell = Sheets("Report").Range("$B$4:$B$21")
Set LookupValueCell = Sheets("Report").Range("$A$4:$A$400")
Set LookupVector = Sheets("Main").Range("$A$4:$A$400")
Set ResultVector = Sheets("Main").Range("$B$4:$B$23")
ResultCell = WorksheetFunction.Lookup(LookupValueCell, LookupVector, ResultVector)
Dim LookupValueCell1 As Range
Dim LookupVector1 As Range
Dim ResultVector1 As Range
Dim ResultCell1 As Range
Set ResultCell1 = Sheets("Report").Range("$E$4:$E$17")
Set LookupValueCell1 = Sheets("Report").Range("$D$4:$D$400")
Set LookupVector1 = Sheets("Main").Range("$A$4:$A$400")
Set ResultVector1 = Sheets("Main").Range("$B$4:$B$372")
ResultCell1 = WorksheetFunction.Lookup(LookupValueCell1, LookupVector1, ResultVector1)
Dim LookupValueCell2 As Range
Dim LookupVector2 As Range
Dim ResultVector2 As Range
Dim ResultCell2 As Range
Set ResultCell2 = Sheets("Report").Range("$H$4:$H$13")
Set LookupValueCell2 = Sheets("Report").Range("$G$4:$H$400")
Set LookupVector2 = Sheets("Main").Range("$A$4:$A$400")
Set ResultVector2 = Sheets("Main").Range("$B$4:$B$372")
ResultCell2 = WorksheetFunction.Lookup(LookupValueCell2, LookupVector2, ResultVector2)
DateString = Format(Now, "DDMMMYYYY")
[B1].Value = DateString
End Sub