I am Logging Current Prices into multiple sheets and Auto Selecting markets 15 minutes before the off.
This is the code I have in Sheets 1, 3, 5 and 7:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
If (Cells(2, 5) = "In Play" And Cells(2, 6) = "") And Cells(1, 20) = 1 Then Call Dummy
If Cells(2, 5) = "Not In Play" And Cells(2, 6) = "" Then Cells(1, 20) = 1
If Cells(2, 5) = "In Play" And Cells(2, 6) = "" Then
If Cells(1, 24) = "" Then Cells(1, 24) = Cells(2, 3)
If Cells(1, 24) <> "" Then Cells(2, 24) = DateDiff("s", Cells(1, 24), Cells(2, 3))
End If
If Cells(2, 5) <> "In Play" And Cells(2, 6) <> "Suspended" Then
Cells(1, 24) = ""
Cells(2, 24) = ""
Cells(1, 26) = ""
End If
If (Cells(2, 5) = "In Play" And Cells(2, 6) = "Suspended") And Cells(1, 26) = "" Then
Range("W1:AH1").Select
Selection.Copy
Range("W1").Activate
ActiveCell.Offset(Cells(1, 27), 23).Activate
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("Q1").Select
Application.CutCopyMode = False
Cells(1, 26) = 1
Cells(1, 27) = Cells(1, 27) + 1
End If
Application.EnableEvents = True
End If
End Sub
This is the code I have in the Module 1:
Sub Dummy()
If Cells(2, 5) = "In Play" And Cells(5, 34) <= -1.01 Then Cells(5, 20) = "DUMMY"
Cells(1, 20) = 2
End Sub
If only one Market is active then the Range("W1:AH1") containing the race data will get get copied as you can see from the screenshot below
- Screenshot Excel.png (171.04 KiB) Viewed 5731 times
If more than one market is active I get the following error as you can see from the screenshot below
- Error 1004.png (13 KiB) Viewed 5731 times