Try this macro on your sample workbook. It adds a sheet named according to the cell B2 in the Summary sheet. This sheet name already exists, so before you run this macro you should rename your sample results sheet to something else, otherwise an error will occur.
The results are not the same as your sample results because your results sheet contains rows which could not have come from the Data sheet (for example, you do not have a Product ABC789 on 23-Jul-13 on your Data sheet, but you do have it on your Sequence 01 sheet).
The macro is not especially robust since I have made a few assumptions, so may not work well with different data, especially if there is more than 1 sequence number in the Summary sheet.In the attached workbook the above macro is executed by pressing Button 1 on the Summary sheet.Code:Sub blah() Set wsAFC = Sheets.Add Set wsResults = Sheets.Add(after:=Sheets(Sheets.Count)) wsResults.Name = Sheets("Summary").Range("B2").Value lr = Sheets("Summary").Cells(Rows.Count, "E").End(xlUp).Row Sheets("Summary").Range("E1:E" & lr).Copy wsAFC.Range("A1") Sheets("Data").Range("I1").Copy wsAFC.Range("B1:C1") Sheets("Summary").Range("C2").Copy wsAFC.Range("B2:B" & lr).FormulaR1C1 = ">=" & Sheets("Summary").Range("C2").Value Sheets("Summary").Range("D2").Copy wsAFC.Range("C2:C" & lr).FormulaR1C1 = "<=" & Sheets("Summary").Range("D2").Value Sheets("Data").Range("A1:T101").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsAFC.Range("A1:C7"), CopyToRange:=wsResults.Range("A1"), Unique:=False Application.DisplayAlerts = False wsAFC.Delete Application.DisplayAlerts = True With wsResults lr = .Cells(.Rows.Count, "K").End(xlUp).Row .Range("U2:U" & lr).FormulaR1C1 = "=VLOOKUP(RC[-10],Summary!C[-16]:C[-15],2,0)" .Range("V2:V" & lr).FormulaR1C1 = "=VLOOKUP(RC[-11],Summary!C[-17]:C[-15],3,0)" .Range("W2:W" & lr).FormulaR1C1 = "=VLOOKUP(RC[-12],Summary!C[-18]:C[-15],4,0)" Sheets("Summary").Range("F1:H1").Copy .Range("U1") End With End Sub
Bookmarks