Import additional coloumn (changes to VBA code)

[Idajespersen]

Dear all excel super users
I have a problem I would like you to help me with  I have the following VBA code, this imports data from a separate file name “query_export_results.csv”, and sort, format etc. In my import file (“query_export_results.csv”) I have added a new coloumn (L) which I would like to import into coloumn O in my master document. It seems simple to make it import an additional coloumn, but I cannot figure out how to do it….
I really hope you can help me!
Many thanks!
Ida


Sub Deviations_site()

Application.ScreenUpdating = False

Dim S: Set S = ActiveWorkbook.Sheets("Deviations")
Dim i, iLastCol, iLastRow, cDOD, cDD: cDOD = 0: cDD = 0

Windows("Deviations_CPH.xlsm").Activate

Sheets("Deviations").Select

iLastCol = S.Cells(9, Columns.Count).End(xlToLeft).Column

Range("A10").Select
iLastRow = Range("A10", Selection.End(xlDown)).Rows.Count + 9

Range("L10").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

Range("A9").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearFormats

Range("A10").Select
Range(Selection, "K10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

Columns("E:E").Select
Selection.Delete Shift:=xlToLeft

On Error Resume Next
Windows("query_export_results.csv").Activate

On Error Resume Next
Windows("query_export_results (1).csv").Activate

On Error Resume Next
Windows("query_export_results (2).csv").Activate

On Error Resume Next
Windows("query_export_results (3).csv").Activate

On Error Resume Next
Windows("query_export_results (4).csv").Activate

On Error Resume Next
Windows("query_export_results (5).csv").Activate

Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Deviations_CPH.xlsm").Activate
Range("A10").Select
ActiveSheet.Paste

Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("E9").Select
ActiveCell.FormulaR1C1 = "Date Due"

Range("A10").Select
iLastRow = Range("A10", Selection.End(xlDown)).Rows.Count + 9

Range("E10").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+21"
Selection.Copy
Range("E10:E" & iLastRow).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


Dim R: Set R = S.Range(S.Cells(9, 1), S.Cells(iLastRow, iLastCol))

S.Range(S.Cells(9, 1), S.Cells(9, iLastCol)).ClearFormats

For i = 1 To iLastCol
If Trim("" & S.Cells(9, i).Text) = "Date of Discovery" Then
cDOD = i
End If
If Trim("" & S.Cells(9, i).Text) = "Date Due" Then
cDD = i
End If
Next

If cDOD = 0 Then
MsgBox "Error: Unable to find Date of Discovery column"
Exit Sub
End If

S.Columns(cDOD).NumberFormat = "dd-mm-yyyy"
If cDD <> 0 Then
S.Columns(cDD).NumberFormat = "dd-mm-yyyy"
End If

For i = 10 To iLastRow
Dim dt: dt = S.Cells(i, cDOD).Value + 21
If cDD <> 0 Then
S.Cells(i, cDD).Value = dt
End If
If dt < Now Then
S.Range(S.Cells(i, 1), S.Cells(i, iLastCol)).Interior.ColorIndex = 2
ElseIf dt - Now <= 7 Then
S.Range(S.Cells(i, 1), S.Cells(i, iLastCol)).Interior.ColorIndex = 3
ElseIf dt - Now <= 14 Then
S.Range(S.Cells(i, 1), S.Cells(i, iLastCol)).Interior.ColorIndex = 44
ElseIf dt - Now <= 21 Then
S.Range(S.Cells(i, 1), S.Cells(i, iLastCol)).Interior.ColorIndex = 36
Else
S.Range(S.Cells(i, 1), S.Cells(i, iLastCol)).Interior.ColorIndex = 4
End If
Next

R.Sort Key1:=S.Range(S.Cells(9, cDOD), S.Cells(iLastRow, cDOD)), Order1:=xlAscending, Header:=xlYes
R.AutoFilter
S.Range(S.Columns(1), S.Columns(iLastCol)).AutoFit

For i = 10 To iLastRow
If (Range("K" & i) = "Closed - Pending Customer Notification" And Range("E" & i) < Now) Then
S.Range(S.Cells(i, 1), S.Cells(i, iLastCol)).Interior.ColorIndex = 7
Else
End If
Next

ActiveWorkbook.Worksheets("Deviations").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Deviations").AutoFilter.Sort.SortFields.Add(Range( _
"K10:K" & iLastRow), xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color _
= RGB(255, 0, 255)
With ActiveWorkbook.Worksheets("Deviations").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Range("L7").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("L10").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range(Selection, "N" & iLastRow), Type:=xlFillValues

Range("A9").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Font.Bold = True
Range("A1").Select

S.Range(S.Columns(1), S.Columns(iLastCol)).AutoFit

Set S = Nothing
Set R = Nothing
End Sub