Sub CopyTextFile()
Dim I As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Worksheets.Add().Name = "PullSheet" 'Make the sheet for the data from txt file to go into
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\Tyger\Desktop\test2.txt", Destination:=Range("$A$1"))
'Change Connection to your file location and Desitnation to where you want to paste
.Name = "test2" 'Name of connection
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
For Each Cell In Range("B1:B6000")
If Cell.Value = "" Then
Cell.Value = "na"
End If
Next
For Each Cell In Range("C1:C6000")
If Cell.Value = "" Then
Cell.Value = "na"
End If
Next
lastRowCOlA = Sheets("PullSheet").Range("A65536").End(xlUp).Row 'Finds he number of the last cell in Column A in Pullsheet
I = 2
For Each Cell In Sheets("PullSheet").Range("A1", Range("A1").Offset(lastRowCOlA, 0)) 'Sets to look in each cell in range of data in column A
Debug.Print Cell
'Start Point 1
lastRowCOlA = wsResults.Range("A65536").End(xlUp).Row
If Cell.Value = "insert_job:" Or Cell.Value = "update_job:" Then 'Checks if cell equals criteria if it doesn't skips to next cell
JobName = Cell.Offset(0, 1).Value ' Sets JobName as the value of the cell one to the right
wsResults.Range("A" & lastRowCOlA + 1).Value = JobName ' Puts JobName in next open cell in results
End If ' Stops Second IF
'Start Point 2
LastRowColB = wsResults.Range("B65536").End(xlUp).Row ' Find Next empty cell in column b of results
If Cell.Value = wsOptions.Range("A" & I).Value Then 'Checks if cell equals criteria
Note = Cell.Offset(0, 1).Value 'Grabs value of cell one to the right
wsResults.Range("B" & LastRowColB + 1).Value = Note 'Inputs value into results page
End If
I = I + 1
LastRowColC = wsResults.Range("C65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("C" & LastRowColC + 1).Value = Note
End If
I = I + 1
LastRowColD = wsResults.Range("D65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("D" & LastRowColD + 1).Value = Note
End If
I = I + 1
LastRowColE = wsResults.Range("E65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("E" & LastRowColE + 1).Value = Note
End If
I = I + 1
LastRowColF = wsResults.Range("F65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("F" & LastRowColF + 1).Value = Note
End If
I = I + 1
LastRowColG = wsResults.Range("G65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("G" & LastRowColG + 1).Value = Note
End If
I = I + 1
LastRowColH = wsResults.Range("H65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("H" & LastRowColH + 1).Value = Note
End If
I = I + 1
LastRowColI = wsResults.Range("I65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("I" & LastRowColI + 1).Value = Note
End If
I = I + 1
LastRowColJ = wsResults.Range("J65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("J" & LastRowColJ + 1).Value = Note
End If
I = I + 1
LastRowColK = wsResults.Range("K65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("K" & LastRowColK + 1).Value = Note
End If
I = I + 1
LastRowColL = wsResults.Range("L65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("L" & LastRowColL + 1).Value = Note
End If
I = I + 1
LastRowColM = wsResults.Range("M65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("M" & LastRowColM + 1).Value = Note
End If
I = I + 1
LastRowColN = wsResults.Range("N65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("N" & LastRowColN + 1).Value = Note
End If
I = I + 1
LastRowColO = wsResults.Range("O65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("O" & LastRowColO + 1).Value = Note
End If
I = I + 1
LastRowColP = wsResults.Range("P65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("P" & LastRowColP + 1).Value = Note
End If
I = I + 1
LastRowColQ = wsResults.Range("Q65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("Q" & LastRowColQ + 1).Value = Note
End If
I = I + 1
LastRowColR = wsResults.Range("R65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("R" & LastRowColR + 1).Value = Note
End If
I = I + 1
LastRowColS = wsResults.Range("S65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("S" & LastRowColS + 1).Value = Note
End If
I = I + 1
LastRowColT = wsResults.Range("T65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("T" & LastRowColT + 1).Value = Note
End If
I = I + 1
LastRowColU = wsResults.Range("U65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("U" & LastRowColU + 1).Value = Note
End If
I = I + 1
LastRowColV = wsResults.Range("V65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("V" & LastRowColV + 1).Value = Note
End If
I = I + 1
LastRowColV = wsResults.Range("V65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("V" & LastRowColV + 1).Value = Note
End If
I = I + 1
LastRowColW = wsResults.Range("W65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("W" & LastRowColW + 1).Value = Note
End If
I = I + 1
LastRowColX = wsResults.Range("X65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("X" & LastRowColX + 1).Value = Note
End If
I = I + 1
LastRowColY = wsResults.Range("Y65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("Y" & LastRowColY + 1).Value = Note
End If
I = I + 1
LastRowColZ = wsResults.Range("Z65536").End(xlUp).Row
If Cell.Value = wsOptions.Range("A" & I).Value Then
Note = Cell.Offset(0, 1).Value
wsResults.Range("Z" & LastRowColZ + 1).Value = Note
End If
I = 2
Next ' Loops Back to check Next Cell in Column A
' Since Jobtype is under a different column than all the other data need new for ecah statement
LastRowColC = Sheets("PullSheet").Range("C65536").End(xlUp).Row ' Finds last cell in column c of pull sheet
For Each Cell In Sheets("PullSheet").Range("C1", Range("C1").Offset(LastRowColC, 0)) 'Sets loop for all items in Colmn C
LastRowColD = wsResults.Range("D65536").End(xlUp).Row ' Sets next Blank row in Results sheet
If Cell.Value = "job_type" Then ' Checks if cell is equal to criteria
Note = Cell.Offset(0, 1).Value ' if does match copies cell one over to the right
wsResults.Range("D" & LastRowColD + 1).Value = Note ' Sets next blank cell in results to found value
End If ' stops second if
Next ' Loops to next cell in Range of Column C we set
Application.DisplayAlerts = False 'Turns off pop up windows so it wont ask you if are sure you want to delete page
Sheets("PullSheet").Delete ' Deletes page ...not needed but I like temp pages and you wanted connection gone
Application.DisplayAlerts = True ' Turns back on
For Each Cell In wsResults.Range("A1:Z6000")
If Cell.Value = "na" Then Cell.Clear
Next
wsResults.Activate
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub