I keep failing on trying to add a fileopen command in VBA
I just want to bring up a dialog box for the user to select a .txt file, which gets arranged as per the instructions in the code.
The below is the code I'm using keeps bringing up a "Compile Error: For Without Next".
Sub InsertData()
ans = MsgBox _
("Please select the report you wish to import.", _
vbOKOnly, "Blah, blah Report")
Dim txtfile As Variant
With Application.FileDialog(msoFileDialogFilePicker)
.Filters.Add "Text files", "*.txt", 1
If .Show = -1 Then
For Each txtfile In .SelectedItems
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & txtfile, Destination:=Sheets("Sheet 1"). _
Range("b65536").End(xlUp).Offset(1, 0))
.Name = filetoimport
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1, 1, 2, 2, 1, 1, 2, 1, 2, 2, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Booking"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Account"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Account Name"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Account Type"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Owner"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Invoice"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Owner 2"
Range("H1").Select
ActiveCell.FormulaR1C1 = "REF 1"
Range("I1").Select
ActiveCell.FormulaR1C1 = "REF 2"
Range("J1").Select
ActiveCell.FormulaR1C1 = "SVC"
Range("K1").Select
ActiveCell.FormulaR1C1 = "SVC type"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Branch"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Branch Name"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Col postcode"
Range("O1").Select
ActiveCell.FormulaR1C1 = "Del postcode"
Range("P1").Select
ActiveCell.FormulaR1C1 = "Legs"
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Miles"
Range("R1").Select
ActiveCell.FormulaR1C1 = "Booked By"
Range("S1").Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A2").Select
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
Range("S1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Time Booked"
Range("T1").Select
ActiveCell.FormulaR1C1 = "Requested Pickup"
Range("U1").Select
ActiveCell.FormulaR1C1 = "Type"
Range("V1").Select
ActiveCell.FormulaR1C1 = "Time 2"
Range("W1").Select
ActiveCell.FormulaR1C1 = "Requested Delivery"
Range("X1").Select
ActiveCell.FormulaR1C1 = "Type"
Range("Y1").Select
ActiveCell.FormulaR1C1 = "Time 2"
Range("Z1").Select
ActiveCell.FormulaR1C1 = "Last Notes"
Range("AA1").Select
ActiveCell.FormulaR1C1 = "Col Arrival"
Range("AB1").Select
ActiveCell.FormulaR1C1 = "POC time"
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Confirmed Delivery Time"
Range("AD1").Select
ActiveWindow.SmallScroll Down:=-12
Range("AE1").Select
ActiveCell.FormulaR1C1 = "Confirmed Time"
Range("AF1").Select
ActiveCell.FormulaR1C1 = "ETA revision 1"
Range("AG1").Select
ActiveCell.FormulaR1C1 = "Reason"
Range("AH1").Select
ActiveCell.FormulaR1C1 = "Revised Time 1"
Range("AI1").Select
ActiveCell.FormulaR1C1 = "ETA revision 2"
Range("AJ1").Select
ActiveCell.FormulaR1C1 = "Reason"
Range("AK1").Select
ActiveCell.FormulaR1C1 = "Revised time 2"
Range("AL1").Select
ActiveCell.FormulaR1C1 = "ETA revision 3"
Range("AM1").Select
ActiveCell.FormulaR1C1 = "Reason"
Range("AN1").Select
ActiveCell.FormulaR1C1 = "Revised Time 2"
Range("AO1").Select
ActiveCell.FormulaR1C1 = "ETA revision 4"
Range("AN1").Select
ActiveCell.FormulaR1C1 = "Revised Time 3"
Range("AP1").Select
ActiveCell.FormulaR1C1 = "Reason"
Range("AQ1").Select
ActiveCell.FormulaR1C1 = "Revised Time 4"
Range("AR1").Select
ActiveCell.FormulaR1C1 = "Del Arrived"
Range("AS1").Select
ActiveCell.FormulaR1C1 = "POD date"
Range("AT1").Select
ActiveCell.FormulaR1C1 = "POD Name"
Range("AU1").Select
ActiveCell.FormulaR1C1 = "Revised Reason"
Range("AV1").Select
ActiveCell.FormulaR1C1 = "Status"
Range("AW1").Select
ActiveCell.FormulaR1C1 = "POD entered by"
Range("AX1").Select
ActiveCell.FormulaR1C1 = "POD entered Time"
Range("AY1").Select
ActiveWindow.SmallScroll Down:=-6
Range("BA1").Select
ActiveCell.FormulaR1C1 = "Revisions"
Range("BB1").Select
ActiveCell.FormulaR1C1 = "Request Number"
Cells.Select
Range("AL1").Activate
Selection.AutoFilter
Cells.Select
Cells.EntireColumn.AutoFit
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("D:G").Select
Selection.EntireColumn.Hidden = True
Columns("K:M").Select
Selection.EntireColumn.Hidden = True
Columns("P:P").Select
Selection.EntireColumn.Hidden = True
Columns("R:R").Select
Selection.EntireColumn.Hidden = True
Columns("S:S").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
Columns("W:W").Select
Selection.Cut
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 18
Columns("AC:AC").Select
Selection.Insert Shift:=xlToRight
Range("X8").Select
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Columns("W:X").Select
Selection.Cut
Columns("AC:AC").Select
Selection.Insert Shift:=xlToRight
Columns("Z:AB").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
Columns("Y:AC").Select
Columns("Y:AC").EntireColumn.AutoFit
Columns("W:W").Select
Selection.EntireColumn.Hidden = True
Columns("Z:Z").Select
Selection.Insert Shift:=xlToRight
Range("Z1").Select
ActiveCell.FormulaR1C1 = "SCORECARD"
Range("Z2").Select
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
Range("S58").Select
Range("Z2").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[56]C[-5]=""BT"",IF(AND(RC[-2]>=RC[-6],RC[-2]<=R[56]C[-12]),""ok"",""BT Fail""),IF(RC[-5]=""AF"",IF(AND(RC[-2]>RC[-6]),""ok"",""AF Fail""),IF(RC[-5]=""BY"",IF(AND(RC[-2]<RC[-6]),""ok"",""BY Fail""))))"
Range("Z2").Select
Selection.AutoFill Destination:=Range("Z2:Z48")
Range("Z2:Z48").Select
Range("Z12").Select
ActiveWindow.SmallScroll Down:=36
Range("Z48").Select
ActiveWindow.SmallScroll Down:=-54
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 43
Columns("AV:AV").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("BC17").Select
Columns("AZ:AZ").ColumnWidth = 8.71
Columns("AZ:BD").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B2").Select
With ActiveWindow
.SplitColumn = 1
.SplitRow = 1
End With
With ActiveWindow
.SplitColumn = 0
.SplitRow = 0
End With
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "Fails"
Range("C2").Select
With ActiveWindow
.SplitColumn = 2
.SplitRow = 1
End With
End Sub
I just want to bring up a dialog box for the user to select a .txt file, which gets arranged as per the instructions in the code.
The below is the code I'm using keeps bringing up a "Compile Error: For Without Next".
Sub InsertData()
ans = MsgBox _
("Please select the report you wish to import.", _
vbOKOnly, "Blah, blah Report")
Dim txtfile As Variant
With Application.FileDialog(msoFileDialogFilePicker)
.Filters.Add "Text files", "*.txt", 1
If .Show = -1 Then
For Each txtfile In .SelectedItems
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & txtfile, Destination:=Sheets("Sheet 1"). _
Range("b65536").End(xlUp).Offset(1, 0))
.Name = filetoimport
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1, 1, 2, 2, 1, 1, 2, 1, 2, 2, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Booking"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Account"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Account Name"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Account Type"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Owner"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Invoice"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Owner 2"
Range("H1").Select
ActiveCell.FormulaR1C1 = "REF 1"
Range("I1").Select
ActiveCell.FormulaR1C1 = "REF 2"
Range("J1").Select
ActiveCell.FormulaR1C1 = "SVC"
Range("K1").Select
ActiveCell.FormulaR1C1 = "SVC type"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Branch"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Branch Name"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Col postcode"
Range("O1").Select
ActiveCell.FormulaR1C1 = "Del postcode"
Range("P1").Select
ActiveCell.FormulaR1C1 = "Legs"
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Miles"
Range("R1").Select
ActiveCell.FormulaR1C1 = "Booked By"
Range("S1").Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A2").Select
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
Range("S1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Time Booked"
Range("T1").Select
ActiveCell.FormulaR1C1 = "Requested Pickup"
Range("U1").Select
ActiveCell.FormulaR1C1 = "Type"
Range("V1").Select
ActiveCell.FormulaR1C1 = "Time 2"
Range("W1").Select
ActiveCell.FormulaR1C1 = "Requested Delivery"
Range("X1").Select
ActiveCell.FormulaR1C1 = "Type"
Range("Y1").Select
ActiveCell.FormulaR1C1 = "Time 2"
Range("Z1").Select
ActiveCell.FormulaR1C1 = "Last Notes"
Range("AA1").Select
ActiveCell.FormulaR1C1 = "Col Arrival"
Range("AB1").Select
ActiveCell.FormulaR1C1 = "POC time"
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Confirmed Delivery Time"
Range("AD1").Select
ActiveWindow.SmallScroll Down:=-12
Range("AE1").Select
ActiveCell.FormulaR1C1 = "Confirmed Time"
Range("AF1").Select
ActiveCell.FormulaR1C1 = "ETA revision 1"
Range("AG1").Select
ActiveCell.FormulaR1C1 = "Reason"
Range("AH1").Select
ActiveCell.FormulaR1C1 = "Revised Time 1"
Range("AI1").Select
ActiveCell.FormulaR1C1 = "ETA revision 2"
Range("AJ1").Select
ActiveCell.FormulaR1C1 = "Reason"
Range("AK1").Select
ActiveCell.FormulaR1C1 = "Revised time 2"
Range("AL1").Select
ActiveCell.FormulaR1C1 = "ETA revision 3"
Range("AM1").Select
ActiveCell.FormulaR1C1 = "Reason"
Range("AN1").Select
ActiveCell.FormulaR1C1 = "Revised Time 2"
Range("AO1").Select
ActiveCell.FormulaR1C1 = "ETA revision 4"
Range("AN1").Select
ActiveCell.FormulaR1C1 = "Revised Time 3"
Range("AP1").Select
ActiveCell.FormulaR1C1 = "Reason"
Range("AQ1").Select
ActiveCell.FormulaR1C1 = "Revised Time 4"
Range("AR1").Select
ActiveCell.FormulaR1C1 = "Del Arrived"
Range("AS1").Select
ActiveCell.FormulaR1C1 = "POD date"
Range("AT1").Select
ActiveCell.FormulaR1C1 = "POD Name"
Range("AU1").Select
ActiveCell.FormulaR1C1 = "Revised Reason"
Range("AV1").Select
ActiveCell.FormulaR1C1 = "Status"
Range("AW1").Select
ActiveCell.FormulaR1C1 = "POD entered by"
Range("AX1").Select
ActiveCell.FormulaR1C1 = "POD entered Time"
Range("AY1").Select
ActiveWindow.SmallScroll Down:=-6
Range("BA1").Select
ActiveCell.FormulaR1C1 = "Revisions"
Range("BB1").Select
ActiveCell.FormulaR1C1 = "Request Number"
Cells.Select
Range("AL1").Activate
Selection.AutoFilter
Cells.Select
Cells.EntireColumn.AutoFit
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("D:G").Select
Selection.EntireColumn.Hidden = True
Columns("K:M").Select
Selection.EntireColumn.Hidden = True
Columns("P:P").Select
Selection.EntireColumn.Hidden = True
Columns("R:R").Select
Selection.EntireColumn.Hidden = True
Columns("S:S").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
Columns("W:W").Select
Selection.Cut
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 18
Columns("AC:AC").Select
Selection.Insert Shift:=xlToRight
Range("X8").Select
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Columns("W:X").Select
Selection.Cut
Columns("AC:AC").Select
Selection.Insert Shift:=xlToRight
Columns("Z:AB").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
Columns("Y:AC").Select
Columns("Y:AC").EntireColumn.AutoFit
Columns("W:W").Select
Selection.EntireColumn.Hidden = True
Columns("Z:Z").Select
Selection.Insert Shift:=xlToRight
Range("Z1").Select
ActiveCell.FormulaR1C1 = "SCORECARD"
Range("Z2").Select
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
Range("S58").Select
Range("Z2").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[56]C[-5]=""BT"",IF(AND(RC[-2]>=RC[-6],RC[-2]<=R[56]C[-12]),""ok"",""BT Fail""),IF(RC[-5]=""AF"",IF(AND(RC[-2]>RC[-6]),""ok"",""AF Fail""),IF(RC[-5]=""BY"",IF(AND(RC[-2]<RC[-6]),""ok"",""BY Fail""))))"
Range("Z2").Select
Selection.AutoFill Destination:=Range("Z2:Z48")
Range("Z2:Z48").Select
Range("Z12").Select
ActiveWindow.SmallScroll Down:=36
Range("Z48").Select
ActiveWindow.SmallScroll Down:=-54
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 43
Columns("AV:AV").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("BC17").Select
Columns("AZ:AZ").ColumnWidth = 8.71
Columns("AZ:BD").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B2").Select
With ActiveWindow
.SplitColumn = 1
.SplitRow = 1
End With
With ActiveWindow
.SplitColumn = 0
.SplitRow = 0
End With
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "Fails"
Range("C2").Select
With ActiveWindow
.SplitColumn = 2
.SplitRow = 1
End With
End Sub
Last edited: