Sub IdentifyAgentAAX0to9() 'Data For AAX0 Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Dispatch_load" Sheets("AMC Agent Breakout").Select Rows("1:1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=8, Criteria1:="KAX0" ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=18, Criteria1:="0" Range("A1:R1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Agent&DRM" Sheets("Agent&DRM").Select Range("C6").Select ActiveSheet.Paste Columns("C:I").Select Selection.Delete Shift:=xlToLeft Columns("D:J").Select Selection.Delete Shift:=xlToLeft Columns("E:F").Select Selection.Delete Shift:=xlToLeft Range("T17").Select ActiveCell.FormulaR1C1 = "AAX0" Range("U17").Select ActiveCell.FormulaR1C1 = "=SUM(R[-10]C[-17]:R[8983]C[-17])" Range("T17:U17").Select Application.CutCopyMode = False Selection.Copy Sheets("Dispatch_load").Select Range("A10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Data For AAX1 Sheets("AMC Agent Breakout").Select Rows("1:1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=8, Criteria1:="KAX1" ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=18, Criteria1:="0" Range("A1:R1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Agent&DRM").Select Range("C6").Select ActiveSheet.Paste Columns("C:I").Select Selection.Delete Shift:=xlToLeft Columns("D:J").Select Selection.Delete Shift:=xlToLeft Columns("E:F").Select Selection.Delete Shift:=xlToLeft Range("T17").Select ActiveCell.FormulaR1C1 = "AAX1" Range("U17").Select ActiveCell.FormulaR1C1 = "=SUM(R[-10]C[-17]:R[8983]C[-17])" Range("T17:U17").Select Application.CutCopyMode = False Selection.Copy Sheets("Dispatch_load").Select Range("A11").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Data For AAX2 Sheets("AMC Agent Breakout").Select Rows("1:1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=8, Criteria1:="KAX2" ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=18, Criteria1:="0" Range("A1:R1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Agent&DRM").Select Range("C6").Select ActiveSheet.Paste Columns("C:I").Select Selection.Delete Shift:=xlToLeft Columns("D:J").Select Selection.Delete Shift:=xlToLeft Columns("E:F").Select Selection.Delete Shift:=xlToLeft Range("T17").Select ActiveCell.FormulaR1C1 = "AAX2" Range("U17").Select ActiveCell.FormulaR1C1 = "=SUM(R[-10]C[-17]:R[8983]C[-17])" Range("T17:U17").Select Application.CutCopyMode = False Selection.Copy Sheets("Dispatch_load").Select Range("A12").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Data For AAX3 Sheets("AMC Agent Breakout").Select Rows("1:1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=8, Criteria1:="KAX3" ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=18, Criteria1:="0" Range("A1:R1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Agent&DRM").Select Range("C6").Select ActiveSheet.Paste Columns("C:I").Select Selection.Delete Shift:=xlToLeft Columns("D:J").Select Selection.Delete Shift:=xlToLeft Columns("E:F").Select Selection.Delete Shift:=xlToLeft Range("T17").Select ActiveCell.FormulaR1C1 = "AAX3" Range("U17").Select ActiveCell.FormulaR1C1 = "=SUM(R[-10]C[-17]:R[8983]C[-17])" Range("T17:U17").Select Application.CutCopyMode = False Selection.Copy Sheets("Dispatch_load").Select Range("A13").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Data For AAX4 Sheets("AMC Agent Breakout").Select Rows("1:1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=8, Criteria1:="KAX4" ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=18, Criteria1:="0" Range("A1:R1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Agent&DRM").Select Range("C6").Select ActiveSheet.Paste Columns("C:I").Select Selection.Delete Shift:=xlToLeft Columns("D:J").Select Selection.Delete Shift:=xlToLeft Columns("E:F").Select Selection.Delete Shift:=xlToLeft Range("T17").Select ActiveCell.FormulaR1C1 = "AAX4" Range("U17").Select ActiveCell.FormulaR1C1 = "=SUM(R[-10]C[-17]:R[8983]C[-17])" Range("T17:U17").Select Application.CutCopyMode = False Selection.Copy Sheets("Dispatch_load").Select Range("A14").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Data For AAX5 Sheets("AMC Agent Breakout").Select Rows("1:1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=8, Criteria1:="KAX5" ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=18, Criteria1:="0" Range("A1:R1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Agent&DRM").Select Range("C6").Select ActiveSheet.Paste Columns("C:I").Select Selection.Delete Shift:=xlToLeft Columns("D:J").Select Selection.Delete Shift:=xlToLeft Columns("E:F").Select Selection.Delete Shift:=xlToLeft Range("T17").Select ActiveCell.FormulaR1C1 = "AAX5" Range("U17").Select ActiveCell.FormulaR1C1 = "=SUM(R[-10]C[-17]:R[8983]C[-17])" Range("T17:U17").Select Application.CutCopyMode = False Selection.Copy Sheets("Dispatch_load").Select Range("A15").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Data For AAX6 Sheets("AMC Agent Breakout").Select Rows("1:1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=8, Criteria1:="KAX6" ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=18, Criteria1:="0" Range("A1:R1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Agent&DRM").Select Range("C6").Select ActiveSheet.Paste Columns("C:I").Select Selection.Delete Shift:=xlToLeft Columns("D:J").Select Selection.Delete Shift:=xlToLeft Columns("E:F").Select Selection.Delete Shift:=xlToLeft Range("T17").Select ActiveCell.FormulaR1C1 = "AAX6" Range("U17").Select ActiveCell.FormulaR1C1 = "=SUM(R[-10]C[-17]:R[8983]C[-17])" Range("T17:U17").Select Application.CutCopyMode = False Selection.Copy Sheets("Dispatch_load").Select Range("A16").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Data For AAX7 Sheets("AMC Agent Breakout").Select Rows("1:1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=8, Criteria1:="KAX7" ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=18, Criteria1:="0" Range("A1:R1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Agent&DRM").Select Range("C6").Select ActiveSheet.Paste Columns("C:I").Select Selection.Delete Shift:=xlToLeft Columns("D:J").Select Selection.Delete Shift:=xlToLeft Columns("E:F").Select Selection.Delete Shift:=xlToLeft Range("T17").Select ActiveCell.FormulaR1C1 = "AAX7" Range("U17").Select ActiveCell.FormulaR1C1 = "=SUM(R[-10]C[-17]:R[8983]C[-17])" Range("T17:U17").Select Application.CutCopyMode = False Selection.Copy Sheets("Dispatch_load").Select Range("A17").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Data For AAX8 Sheets("AMC Agent Breakout").Select Rows("1:1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=8, Criteria1:="KAX8" Application.Wait (Now + TimeValue("00:00:02")) ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=18, Criteria1:="0" Application.Wait (Now + TimeValue("00:00:02")) Range("A1:R1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Application.Wait (Now + TimeValue("00:00:02")) Sheets("Agent&DRM").Select Range("C6").Select Application.Wait (Now + TimeValue("00:00:04")) ActiveSheet.Paste Columns("C:I").Select Selection.Delete Shift:=xlToLeft Application.Wait (Now + TimeValue("00:00:02")) Columns("D:J").Select Selection.Delete Shift:=xlToLeft Application.Wait (Now + TimeValue("00:00:02")) Columns("E:F").Select Selection.Delete Shift:=xlToLeft Application.Wait (Now + TimeValue("00:00:02")) Range("T17").Select ActiveCell.FormulaR1C1 = "AAX8" Range("U17").Select ActiveCell.FormulaR1C1 = "=SUM(R[-10]C[-17]:R[8983]C[-17])" Range("T17:U17").Select Application.CutCopyMode = False Selection.Copy Sheets("Dispatch_load").Select Range("A18").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Data For AAX9 Sheets("AMC Agent Breakout").Select Rows("1:1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=8, Criteria1:="KAX9" ActiveSheet.Range("$A$1:$R$18245").AutoFilter Field:=18, Criteria1:="0" Range("A1:R1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Agent&DRM").Select Range("C6").Select ActiveSheet.Paste Columns("C:I").Select Selection.Delete Shift:=xlToLeft Columns("D:J").Select Selection.Delete Shift:=xlToLeft Columns("E:F").Select Selection.Delete Shift:=xlToLeft Range("T17").Select ActiveCell.FormulaR1C1 = "AAX9" Range("U17").Select ActiveCell.FormulaR1C1 = "=SUM(R[-10]C[-17]:R[8983]C[-17])" Range("T17:U17").Select Application.CutCopyMode = False Selection.Copy Sheets("Dispatch_load").Select Range("A19").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub