tiraparson
New Member
- Joined
- Jan 11, 2019
- Messages
- 3
If anyone can audit and tell me whats wrong. When I step through the code everything works but as a whole it crashes.
Code:
[TABLE="width: 1500, align: left"]
<tbody>[TR]
[TD]Sub ImportTextFile()
Application.EnableEvents = False
Application.DisplayAlerts = False
'IMPORTS TICKETS
Dim vFileName
vFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
If vFileName = False Or Right(vFileName, 3) <> "txt" Then
GoTo BeforeExit
End If
Workbooks.OpenText Filename:=vFileName, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, _
TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
'Removing extra rows from the top.
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Columns("A:J").Select
'COPY TO CURRENT WORKBOOK
Cells.Select
Selection.Copy
ActiveWorkbook.Close False
Windows("TICKET AUTOMATION.xlsm").Activate
Sheets("ALL TICKETS").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(9, 1), Array(19, 1), Array(48, 1), Array(59, 1), _
Array(66, 1), Array(79, 1), Array(95, 1), Array(102, 1), Array(121, 1)), _
TrailingMinusNumbers:=True
Columns("F:G").Select
Range("G1").Activate
Selection.Delete Shift:=xlToLeft
Cells.Select
Cells.EntireColumn.AutoFit
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
Columns("A:H").Select
Range("H1").Activate
ActiveWorkbook.Worksheets("ALL TICKETS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("ALL TICKETS").Sort.SortFields.Add Key:=Range( _
"E2:E630"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("ALL TICKETS").Sort
.SetRange Range("A1:H630")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
'ADD NEW SHEETS
With ThisWorkbook
.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "TRIAGE"
.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "CON"
.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "PARTS"
.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "ESC"
.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "TECHS"
'ADDING HEADER
Sheets("ALL TICKETS").Select
Range("A1:H1").Select
Selection.Copy
Sheets("TRIAGE").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("CON").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("PARTS").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("ESC").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("TECHS").Select
Range("A1").Select
ActiveSheet.Paste
'MOVE TICKETS
Call CopyTECHS
Call CopyTRIAGE
Call CopyCON
Call CopyPARTS
Call CopyESC
'AUTOFIT
Sheets("TRIAGE").Select
Columns("A:H").Select
Columns("A:H").EntireColumn.AutoFit
Sheets("CON").Select
Columns("A:H").Select
Columns("A:H").EntireColumn.AutoFit
Sheets("PARTS").Select
Columns("A:H").Select
Columns("A:H").EntireColumn.AutoFit
Sheets("ESC").Select
Columns("A:H").Select
Columns("A:H").EntireColumn.AutoFit
Sheets("TECHS").Select
Columns("A:H").Select
Columns("A:H").EntireColumn.AutoFit
'SAVE AS
Dim FName As String
Dim FPath As String
FPath = "http://collaboration.ad.qintra.com/BU/Network/scph/HomeSecurityOnline/Shared%20Documents/METRICS"
FName = Sheets("MACRO").Range("A2").Text
ThisWorkbook.SaveAs Filename:=FPath & "" & "TICKETS" & " " & FName & " " & Range("MACRO!$A$2")
End With
BeforeExit:
Application.EnableEvents = True
Application.DisplayAlerts = True
End With
End With
End With
End Sub
Sub CopyTECHS()
Dim i As Integer
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("ALL TICKETS")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("TECHS")
For i = 2 To ws1.Range("E65536").End(xlUp).Row
If ws1.Cells(i, 5) = Range("LIST!$A$2") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$A$3") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$A$4") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$A$5") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$A$6") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$A$7") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$A$8") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$A$9") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$A$10") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$A$12") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$A$13") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$A$14") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$A$15") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$A$16") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$A$17") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
Next i
End Sub
Sub CopyCON()
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("ALL TICKETS")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("CON")
For i = 2 To ws1.Range("E65536").End(xlUp).Row
If ws1.Cells(i, 5) = Range("LIST!$B$2") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$B$3") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$B$4") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$B$5") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$B$6") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$B$7") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$B$8") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$B$9") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$B$10") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$B$12") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$B$13") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$B$14") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$B$15") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$B$16") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$B$17") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
Next i
End Sub
Sub CopyESC()
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("ALL TICKETS")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("ESC")
For i = 2 To ws1.Range("E65536").End(xlUp).Row
If ws1.Cells(i, 5) = Range("LIST!$C$2") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$C$3") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$C$4") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$C$5") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$C$6") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$C$7") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$C$8") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$C$9") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$C$10") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$C$12") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$C$13") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$C$14") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$C$15") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$C$16") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$C$17") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
Next i
End Sub
Sub CopyPARTS()
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("ALL TICKETS")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("PARTS")
For i = 2 To ws1.Range("E65536").End(xlUp).Row
If ws1.Cells(i, 5) = Range("LIST!$D$2") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$D$3") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$D$4") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$D$5") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$D$6") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$D$7") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$D$8") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$D$9") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$D$10") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$D$12") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$D$13") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$D$14") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$D$15") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$D$16") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$D$17") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
Next i
End Sub
Sub CopyTRIAGE()
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("ALL TICKETS")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("TRIAGE")
For i = 2 To ws1.Range("E65536").End(xlUp).Row
If ws1.Cells(i, 4) = Range("LIST!$E$2") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 4).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$E$3") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$E$4") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$E$5") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$E$6") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$E$7") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$E$8") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$E$9") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$E$10") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$E$12") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$E$13") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$E$14") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$E$15") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$E$16") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
If ws1.Cells(i, 5) = Range("LIST!$E$17") Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 5).End(xlUp).Row + 1)
Next i
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]
Last edited by a moderator: