Option Explicit
Sub coupon_loop()
Dim lrow As Long, i As Long
Dim fpath As String 'directory to save csv files to
Dim rDel As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.StatusBar = "Creating CSV Files"
'Store current prices in temporary sheets so prices that have not changed on the update can be deleted later
With Worksheets("Coupon")
lrow = .Range("D" & .Rows.Count).End(xlUp).Row
For i = 4 To lrow
If .Cells(i, 8) >= Cells(i, 54) And .Cells(i, 9) >= .Cells(i, 55) Then GoTo GetNext
Worksheets("Selections").Range("Z2").Value = .Range("J" & i).Value ' home goals
Worksheets("Selections").Range("Z4").Value = .Range("K" & i).Value ' away goals
Worksheets("Selections").Range("AB158").Value = .Range("L" & i).Value '1st half goals %
Worksheets("Selections").Range("AB237").Value = .Range("M" & i).Value '2nd half goals %
Worksheets("Selections").Range("AA3").Value = .Range("R" & i).Value '1x2 Factor
Worksheets("Selections").Range("AG2").Value = .Range("S" & i).Value 'Draw Factor
Worksheets("Selections").Range("A2:U360").Copy
Worksheets("SelectionsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
With Sheet17
.Range("P2", .Range("P2").End(xlDown).Offset(, 1)).Copy .Range("BA2")
End With
'** Copy current match from Events, Markets & Selections to next available row in temporary sheets
With Worksheets("Coupon")
lrow = .Range("D" & .Rows.Count).End(xlUp).Row
For i = 4 To lrow
If .Cells(i, 8) >= Cells(i, 54) And .Cells(i, 9) >= .Cells(i, 55) Then GoTo GetNext
Worksheets("Selections").Range("B2").Value = .Range("D" & i).Value
Worksheets("Selections").Range("C2").Value = .Range("E" & i).Value
Worksheets("Selections").Range("E2").Value = .Range("AV" & i).Value
Worksheets("Selections").Range("Z2").Value = .Range("J" & i).Value
Worksheets("Selections").Range("Z4").Value = .Range("K" & i).Value
Worksheets("Selections").Range("G2").Value = .Range("F" & i).Value
Worksheets("Selections").Range("G4").Value = .Range("G" & i).Value
Worksheets("Selections").Range("AB158").Value = .Range("L" & i).Value
Worksheets("Selections").Range("AB237").Value = .Range("M" & i).Value
Worksheets("Selections").Range("AA3").Value = .Range("R" & i).Value
Worksheets("Selections").Range("AG2").Value = .Range("S" & i).Value
Worksheets("Markets").Range("AB2").Value = .Range("V" & i).Value
Worksheets("Markets").Range("AD2").Value = .Range("W" & i).Value
Worksheets("Events").Range("A2:W2").Copy
Worksheets("EventsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Worksheets("Markets").Range("A2:AB85").Copy
Worksheets("MarketsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Sheets("MarketsTemporary").Select
Set rDel = Nothing
With ActiveSheet.UsedRange
.Value = .Value
For Each cell In Intersect(.Cells, .Columns("A"))
If Len(cell.Text) = 0 Then
If rDel Is Nothing Then Set rDel = cell
Set rDel = Union(rDel, cell)
End If
Next cell
End With
If Not rDel Is Nothing Then rDel.EntireRow.Delete
Worksheets("Selections").Range("A2:U360").Copy
Worksheets("SelectionsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Sheets("SelectionsTemporary").Select
Set rDel = Nothing
With ActiveSheet.UsedRange
.Value = .Value
For Each cell In Intersect(.Cells, .Columns("A"))
If Len(cell.Text) = 0 Then
If rDel Is Nothing Then Set rDel = cell
Set rDel = Union(rDel, cell)
End If
Next cell
End With
If Not rDel Is Nothing Then rDel.EntireRow.Delete
GetNext: Next i
End With
*****Delete rows where price has not changed *******
'Need to add some code here that deletes any row where the value in column P is the same as the value in column BA
' Remove underscores in event classes and types on temporary sheets
Sheets("EventsTemporary").Select
Columns("B:C").Select
Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("MarketsTemporary").Select
Columns("B:C").Select
Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("SelectionsTemporary").Select
Columns("B:C").Select
Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
fpath = "C:\Documents and Settings\HOME USER\My Documents\Dropbox\_Work to be done\Footy Model csv"
'fpath="C:\Documents and Settings\HOME USER\My Documents\Dropbox\_Work to be done\Footy Model csv"
'fpath="C:\Users\Adam\Dropbox\_Work to be done"
'** Save temporary sheets as csv files in directory named fpath
ThisWorkbook.Worksheets("EventsTemporary").Copy
ActiveWorkbook.SaveAs Filename:=fpath & "\Events - " & Format(Now, "yyyy-mm-dd hh-mm-ss") & " - " & Environ$("username") & ".csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=True
ThisWorkbook.Worksheets("MarketsTemporary").Copy
ActiveWorkbook.SaveAs Filename:=fpath & "\Markets - " & Format(Now, "yyyy-mm-dd hh-mm-ss") & " - " & Environ$("username") & ".csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=True
ThisWorkbook.Worksheets("SelectionsTemporary").Copy
ActiveWorkbook.SaveAs Filename:=fpath & "\Selections - " & Format(Now, "yyyy-mm-dd hh-mm-ss") & " - " & Environ$("username") & ".csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=True
'** Clear contents of temporary sheets
lrow = ThisWorkbook.Worksheets("EventsTemporary").Range("A" & Rows.Count).End(xlUp).Row
ThisWorkbook.Worksheets("EventsTemporary").Range("A2:W" & lrow).ClearContents
lrow = ThisWorkbook.Worksheets("MarketsTemporary").Range("A" & Rows.Count).End(xlUp).Row
ThisWorkbook.Worksheets("MarketsTemporary").Range("A2:AD" & lrow).ClearContents
lrow = ThisWorkbook.Worksheets("SelectionsTemporary").Range("A" & Rows.Count).End(xlUp).Row
ThisWorkbook.Worksheets("SelectionsTemporary").Range("A2:Y" & lrow).ClearContents
'** store latest TG & Supremacies
With Sheet2
.Range("H4", .Range("H4").End(xlDown).Offset(, 1)).Copy .Range("BB4")
End With
ThisWorkbook.Worksheets("Coupon").Activate
ThisWorkbook.Worksheets("Coupon").Range("A1").Select
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub
Bookmarks