PDA

View Full Version : VBA Code: excel is hanging at some point


murthysri272
03-03-2016, 02:41 PM
Hi,
While I am executing the attached script, excel is hanging at some point (Data For AAX7 / Data For AAX8). Please let me know what can I do to avoid this scenario. Your help on this issues is much appreciated.

I think this issue is happening at the time of copying the data after filtering. It looks like, It has lot many rows to copy and paste; may be that's why it is hanging. More over when I am executing the each line step by step I am not getting any issue.
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
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 = "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





Regards,
Sri