- Code: Select all
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo xit
Dim x As Integer, y As Integer
Dim Wb As Workbook
Set Wb = ThisWorkbook
x = Wb.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
y = Wb.Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row
If Target.Columns.Count <> 7 Then Exit Sub 'Amend the number to the cells that change with the BSP data
Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
Application.Calculation = xlCalculationManual
If Wb.Sheets("Sheet1").Range("A1").Value = Wb.Sheets("Sheet1").Range("T1").Value Then GoTo xit
If Wb.Sheets("Sheet1").Range("E2").Value = "In Play" Then
Wb.Sheets("Sheet1").Range("T1").Value = Wb.Sheets("Sheet1").Range("A1").Value
Wb.Sheets("Sheet3").Range("A" & y + 1 & ":Z" & x + y).Value = Wb.Sheets("Sheet1").Range("A1:Z" & x).Value
End If
xit:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True 'Turn on events again
End Sub